/**IMPORT OTHER KITCHEN CATEGORIES DATA FROM AMAZON***/
PROC IMPORT 
DATAFILE ="D:\Amazon Price Dynamics\New Data\Data Scraped\Home_Kitchen\merged\kitchenall_amazon_4_7_20.csv"
OUT= amazon_kitchnequip3 DBMS=csv
REPLACE;
run;

PROC IMPORT 
DATAFILE ="D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\luggage_boardgamesdata_4_6_2020.csv"
OUT= amazon_luggageboardgames DBMS=csv
REPLACE;
run;
proc contents data = amazon_kitchnequip3;
run;
/**selecting only deep fryer and microwave; water pitcher data not available in the external sites and electric cooker is the focal category**/
data amazon_kitchnequip4 (keep = time1 ASIN seller_ID brand categ Answered_Questions price1 Buy_Box_Price1 Seller_Rating
num_seller_Posrating  num_seller_ratings Product_Star_Rate Sales_Rank_Own_Categ Sales_Rank_Sub_categ 
Product_reviews condition Freeship  FBA Prime Instock season);
set amazon_kitchnequip3;
where categ in ('Deep Fryer', 'Microwave');
if Buy_Box_Price1 = . then Nobuyboxwinner = 1;
else Nobuyboxwinner = 0;
run;
PROC SORT DATA = amazon_kitchnequip4 NODUPKEY OUT= amazon_kitchnequip4;
BY TIME1 ASIN Seller_ID condition ;
RUN;


data amazon_luggageboardgames1 (keep = time1 ASIN seller_ID brand categ Answered_Questions price1 Buy_Box_Price1 Seller_Rating
num_seller_Posrating  num_seller_ratings Product_Star_Rate Sales_Rank_Own_Categ Sales_Rank_Sub_categ 
Product_reviews condition Freeship  FBA Prime Instock season);
set amazon_luggageboardgames;
if Answered_Questions  = . then Answered_Questions  = 0;
run;
/*stacking data sets*/
data amazon_allothers;
set amazon_kitchnequip4 amazon_luggageboardgames1;
run;
proc freq data = amazon_allothers;
tables categ;
run;

/***3P SELLER CHARACTERISTICS***/

/****RESTRICTING DATA TO THE TIME LENGTH EXTERNAL SITE INFORMATION IS AVAILABLE***/

proc freq data = amazon_allothers1;
tables brand;
run;
data amazon_allothers1;
SET amazon_allothers;
where time1 >= '14Dec2017'd and time1 <= '06May2018'd ;
/*length brand1 $50.;*/
/*brand1 = brand;*/
RUN;

/****price change by ASIN***/

/**COMPUTING PRICE CHANGE BY ID, BRAND AND CATEG***/

proc sort data = amazon_allothers1 NODUPkey out=amazon_allothers2 ;
by  ASIN seller_ID time1 ;
run;
proc expand data=amazon_allothers2 out=amazon_allothers3 method = none; 
  by ASIN seller_ID ;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_amazonothers as
select 
time1,
ASIN,
categ,
brand,
seller_ID,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange

from
amazon_allothers3
group by
time1,
ASIN,
categ,
brand,
seller_ID;
quit;
/***count number of price changes by seller type and ASIN***/
proc sql;
create table numpricechange_amazonothers  as
select
ASIN,
categ,
brand,
seller_ID,
count(distinct time1) as num_days,
count(distinct pricechange) as num_pricechange
from
price_changes_amazonothers
group by
ASIN,
categ,
brand,
seller_ID;
quit;
proc sort data = numpricechange_amazonothers;
by categ;
run;
proc univariate data = numpricechange_amazonothers;
var num_days;
by categ;
/*where categ = 'Microwave';*/
run;

proc freq data =numpricechange_amazonothers;
tables categ;
run;

/**selecting 3P sellers who have been on the marketplace > 10 days**/
DATA REDUCED_SELLERS;
SET numpricechange_amazonothers;
WHERE num_days > 10;
RUN;


proc sort data = REDUCED_SELLERS (keep = ASIN seller_ID) nodupkey out = reduced_seller_list;
by ASIN seller_ID;
run;
proc sort data = amazon_allothers1 out = amazon_allothers_sort;
by ASIN seller_ID;
run;
data amazon_allothers_finalsellers;
merge amazon_allothers_sort (in=a) reduced_seller_list (in=b);
by ASIN seller_ID;
if a and b;
run;

/**classifying used and new product sellers**/

data amazon_allothers_usednew;
set amazon_allothers_finalsellers;
length seller_type $50.;
IF CONDITION = 0 AND SELLER_ID = 'Amazon'  THEN SELLER_TYPE = 'Amazon Old';
ELSE IF CONDITION = 1 AND SELLER_ID = 'Amazon' THEN SELLER_TYPE = 'Amazon New';
ELSE IF CONDITION = 0 AND SELLER_ID NE 'Amazon' THEN SELLER_TYPE = '3P Old';
ELSE IF CONDITION = 1 AND SELLER_ID NE 'Amazon' THEN SELLER_TYPE = '3P New';
run;
proc freq data = amazon_allothers_usednew;
tables condition seller_type;
run;

proc print data = amazon_allothers_usednew (obs=10);
var seller_id;
where seller_type in ( '3P Old','3P New');
run;


/***DETERMINING SELLER CHARACTERISTICS FOR EACH SELLER***/

data amazon_allothers_new;
set amazon_allothers_usednew;
where seller_type = '3P New';
run;
proc freq data = amazon_allothers_usednew;
tables FBA*seller_type /list;
where categ = 'Microwave';
run;

proc sql;
create table SELLER_VARS as
select 
categ,
Seller_ID,
SELLER_TYPE,
count(ASIN) as num_ASIN_sold,
count(distinct ASIN) as num_uniq_ASIN_sold,
mean(condition) as mean_condition,
mean(freeship) as mean_freeship,
mean(FBA) as mean_FBA,
mean(Prime) as mean_prime,
MEAN(seller_rating) as MEAN_SELLER_RATE,
min(seller_rating) as Min_SELLER_RATE,
max(seller_rating) as Max_SELLER_RATE,
MAX(num_seller_Posrating) AS MAX_PERC_POSRATING,
Min(num_seller_Posrating) AS Min_PERC_POSRATING,
Mean(num_seller_Posrating) AS Mean_PERC_POSRATING,
MAX(num_seller_ratings) AS MAX_NUM_RATINGS,
Min(num_seller_ratings) AS Min_NUM_RATINGS,
Mean(num_seller_ratings) AS Mean_NUM_RATINGS,
MEAN(price1) as MEAN_PRICE,
Max(price1) as Max_PRICE,
Min(price1) as Min_PRICE
from 
amazon_allothers_new
group by
categ,
Seller_ID,
SELLER_TYPE
;
quit;

PROC EXPORT 
DATA=SELLER_VARS
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\3psellercharac_4_15_20.csv"
REPLACE;
run;
proc contents data = SELLER_VARS;
run;
proc means data = SELLER_VARS n mean std nway;
class categ;
var 
MEAN_SELLER_RATE
MAX_NUM_RATINGS
MAX_PERC_POSRATING
mean_FBA
mean_freeship
num_uniq_ASIN_sold;
output out = summ_sellercharc ;
run;

proc standard data = SELLER_VARS  mean=0 std=1 out= SELLER_standvars_othercateg;
VAR 
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_PRICE
MEAN_SELLER_RATE
Max_PRICE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
mean_FBA
mean_prime
mean_condition
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
;
by categ;
RUN;
/*PROC PRINT DATA = SELLER_standvars_Cooker (obs=20);*/
/*RUN;*/

/**RUNNING VARCLUS PROCEDURE TO REMOVE CORRELATED VARIABLES**/
proc varclus data=SELLER_standvars_othercateg    outtree=tree centroid MAXITER = 1000 maxclusters= 40  CENTROID;
var 
Mean_NUM_RATINGS
Mean_PERC_POSRATING
mean_FBA
mean_prime
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold
;
by categ;
run;

/**RUNNING FASTCLUS PROCEDURE for each category***/

data deepfryer_clusteranalysis;
set SELLER_standvars_othercateg;
where categ = 'Deep Fryer';
run;

proc fastclus data = deepfryer_clusteranalysis
 out= sellers_clusters_deepfryer
 maxc= 10 converge = 0 maxiter=100000 summary;
 var 
Mean_NUM_RATINGS
/*Mean_PERC_POSRATING*/
mean_FBA
/*mean_prime*/
mean_freeship
num_ASIN_sold
/*num_uniq_ASIN_sold*/
;
run;
PROC EXPORT 
DATA=sellers_clusters_deepfryer
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\clusteroutput_deepfryer_4_23_20.csv"
REPLACE;
run;

/***clubbing clusters**/
/****CLUBBING CLOSE CLUSTERS TO GET FINAL SET OF CLUSTERS****/

DATA sellers_clusters_deepfryer1;
SET sellers_clusters_deepfryer;
IF CLUSTER IN (1,4)  THEN CLUSTER1 = 1;
ELSE IF CLUSTER IN (2,5) THEN CLUSTER1 = 2;
ELSE IF CLUSTER = 3  THEN CLUSTER1 = 3;
ELSE IF CLUSTER IN (6,8) then CLUSTER1 = 4;
ELSE CLUSTER1 = 5;
RUN;
/*PROC FREQ DATA = sellers_clusters_deepfryer1;*/
/*TABLES CLUSTER1;*/
/*RUN;*/

/***MERGING CLUSTERS BACK TO THE UN-STANDARDIZED DATA SET TO RECOVER MEAN CLUSTER CHARACTERISTICS****/

PROC SORT DATA = sellers_clusters_deepfryer1 (KEEP= Seller_ID Cluster1 ) OUT=sellers_clusters_deepfryer_SORT;
BY Seller_ID;
RUN;
DATA SELLER_VARS_DEEPFRYER;
SET SELLER_VARS;
WHERE CATEG = 'Deep Fryer';
RUN;

PROC SORT DATA = SELLER_VARS_DEEPFRYER out=SELLER_VARS_DEEPFRYER_sort;
BY Seller_ID;
RUN;

data seller_DEEPFRYER_clusters (drop= cluster1);
merge sellers_clusters_deepfryer_SORT (in=a) SELLER_VARS_DEEPFRYER_sort (in=b);
BY Seller_ID;
if a and b;
IF CLUSTER1 = 1 THEN seller_type = 'Cluster 1';
if CLUSTER1 = 2 THEN seller_type = 'Cluster 2';
if CLUSTER1 = 3 THEN seller_type = 'Cluster 3';
if CLUSTER1 = 4 THEN seller_type = 'Cluster 4';
if CLUSTER1 = 5 THEN seller_type = 'Cluster 5';
run;
PROC CONTENTS DATA = numpricechange_amazonothers;
RUN;
proc means data = seller_DEEPFRYER_clusters n mean min max std nway ;
class seller_type;
var
MAX_NUM_RATINGS
MAX_PERC_POSRATING
MEAN_SELLER_RATE
Max_SELLER_RATE
Mean_NUM_RATINGS
Mean_PERC_POSRATING
Min_NUM_RATINGS
Min_PERC_POSRATING
Min_PRICE
Min_SELLER_RATE
mean_FBA
mean_prime
mean_condition
mean_freeship
num_ASIN_sold
num_uniq_ASIN_sold;
output out= seller_DEEPFRYER_cluster_charac ;
run;

PROC EXPORT 
DATA=seller_DEEPFRYER_cluster_charac
DBMS=csv
OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\clusterPROFILEs_deepfryer_4_23.csv"
REPLACE;
run;
/***MERGING CLUSTER OUTPUT WITH THE NUMBER OF PRICE CHANGES AND NUMBER OF DAYS TREND***/
data numpricechange_deepfryer;
set numpricechange_amazonothers;
where categ = 'Deep Fryer';
run;

PROC SORT DATA = numpricechange_deepfryer OUT= SORTED_PRICECHANGES;
BY SELLER_ID;
RUN;
PROC SORT DATA = seller_DEEPFRYER_clusters out= sorted_sellerclusters;
by seller_id;
run;
DATA SELLER_PRICECHANGE_DEEPFRYER;
MERGE SORTED_PRICECHANGES (IN=A) sorted_sellerclusters (IN=B);
BY SELLER_ID;
IF A AND B;
RUN;
/***above file exported as PRICECHANGES_SELLERCLUSTER_DEEPFRYER_4_24.csv****/

/******MERGING CLUSTER OUTPUT WITH THE DAILY PRICE TREND DATA AT ASIN LEVEL***/

proc freq data = amazon_allothers_usednew;
tables seller_type;
run;
proc contents data = amazon_allothers_usednew;
run;
data deepfryer_usednew;
set amazon_allothers_usednew;
where categ = 'Deep Fryer';
run;
/***ADDED VARIABLES AFTER CONVO WITH BRYAN --- NEED TO INCLUDE THESE IN THE SELLER ENTRY AND BUY BOX MODELS***/
/**SELLER ENTRY: NON BUY BOX 3P PRICE; BUY BOX MODEL: DIFFERENCE FROM LOWEST PRICE OFFER***/


proc sql;
create table price_dev_BB as
select 
time1,
ASIN,
Seller_ID,
(price1-Buy_Box_Price1)as dev_BBprice

from 
deepfryer_usednew
group by
time1,
ASIN,
Seller_ID
;
quit;
proc sql;
   create table lowestprice_offer as
   select 
      time1,
      ASIN,
/*      Buy_Box_Price1,*/
      min(price1) as lowestprice_offer
   from deepfryer_usednew
   group by time1, ASIN;
quit;

data non_buybox3pprice;
set deepfryer_usednew;
where seller_id ne 'Amazon' and price1 ne Buy_Box_Price1;
run;
proc sql;
   create table mean_nonbuybox3pprice as
   select 
      time1,
      ASIN,
      mean(price1) as nonbuybox3p_price
   from non_buybox3pprice
   group by time1, ASIN;
quit;

/***adding the mean non buy box price for the seller entry model*****/
proc sort data = mean_nonbuybox3pprice;
by time1 ASIN;
run;

proc sort data = deepfryer_usednew out = focal_rival_prods26_sort;
by time1 ASIN;
run;

data focal_rival_prods27;
merge focal_rival_prods26_sort (in=a) mean_nonbuybox3pprice (in=b) ;
by time1 ASIN;
if a and b ;
run;
proc freq data = focal_rival_prods27;
tables asin*brand / list;
run;
proc means data = focal_rival_prods27 n sum nway noprint;
class time1 ASIN seller_id seller_type;
var price1 Buy_Box_Price1 nonbuybox3p_price Seller_Rating num_seller_Posrating  num_seller_ratings condition Freeship  FBA Prime Instock ;
output out = price_trend sum= ;
run;
proc print data = price_trend;
var nonbuybox3p_price;
where ASIN = 'B00NQ7QFGM';
run;

PROC SORT DATA = price_trend OUT= price_trend1;
BY SELLER_ID ;
RUN;
data price_trend2 (drop=seller_type);
set price_trend1;
where seller_type = '3P New';
run;
data price_trend3;
set price_trend1;
where seller_type ne '3P New';
run;

PROC SORT DATA = seller_DEEPFRYER_clusters (KEEP= SELLER_ID seller_type) OUT= SELLER_TYPE1;
BY SELLER_ID ;
RUN;

DATA newSELLER_TYPE_TREND ;
MERGE SELLER_TYPE1 (IN=A) price_trend2 (IN=B) ;
BY SELLER_ID ;
IF B;
RUN;
/*proc print data = newSELLER_TYPE_TREND (obs=20);*/
/*run; */
data SELLER_TYPE_TREND;
set newSELLER_TYPE_TREND price_trend3;
run;

PROC SORT DATA = SELLER_TYPE_TREND;
BY TIME1 ASIN;
RUN;

DATA SELLER_TYPE_TREND1;
MERGE SELLER_TYPE_TREND (IN=A) lowestprice_offer (IN=B);
BY TIME1 ASIN;
IF A ;
RUN;
proc print data = SELLER_TYPE_TREND1 (obs=10);
run;
/****EXTRA SUMMARY NEEDED FOR DEVIATION FROM BUY BOX PRICE - THIS VARIABLE IS NOT INCLUDED IN THE FINALDATA USED FOR MODELING**/
proc sort data = price_dev_BB;
by time1 asin seller_id;
run;
proc sort data = SELLER_TYPE_TREND1;
by time1 asin seller_id;
run;

data devbuybox_dfryer;
merge seller_type_trend1 (in=a) price_dev_bb (in=b);
by time1 asin seller_id;
if a and b;
run;
proc print data = devbuybox_dfryer (obs=10);
/*var nonbuybox3p_price;*/
/*where ASIN = 'B00NQ7QFGM';*/
run;
PROC MEANS DATA = devbuybox_dfryer N SUM MEAN MAX MIN NWAY;
CLASS SELLER_TYPE;
VAR dev_BBprice;
OUTPUT OUT = DEV_SUMMARY;
RUN;

/*****************************************/

/*proc freq data = SELLER_TYPE_TREND;*/
/*tables seller_type;*/
/*run;*/
/**/
/*PROC EXPORT */
/*DATA=SELLER_TYPE_TREND*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\Sellerclusters_deepfryer_Apr2020.csv"*/
/*REPLACE;*/
/*run;*/
/*proc contents data = SELLER_TYPE_TREND12;*/
/*run;*/

PROC EXPORT 
DATA=SELLER_TYPE_TREND1
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\Sellerclusters_deepfryer_July2025.csv"
REPLACE;
run;

/**COMPUTING PRICE CHANGE BY ASIN AND SELLER TYPE***/

proc sort data = SELLER_TYPE_TREND1 NODUPREC out=SELLER_TYPE_TREND11 ;
by  ASIN seller_type seller_ID TIME1 ;
run;
proc expand data=SELLER_TYPE_TREND11 out=SELLER_TYPE_TREND12 method = none; 
  by ASIN seller_type seller_ID;
  convert price1 = price_lag1     / transformout=(lag 1); 
RUN;

proc sql;
create table price_changes_sellertype as
select 
time1,
ASIN,
seller_type,
seller_ID,
price1,
(price1 - lowestprice_offer) as pricediff_fromlowest,
(case when price_lag1 ne . and price_lag1 ne 0 then (price1-price_lag1)/price_lag1 else 0 end) as pricechange
from
SELLER_TYPE_TREND12
group by
time1,
ASIN,
seller_type,
seller_ID;
quit;
proc print data = price_changes_sellertype;
var pricechange;
where seller_type = 'Cluster 5' and ASIN = 'B01JCECNM2';
run;

/*PROC EXPORT */
/*DATA=price_changes_sellertype*/
/*DBMS=csv*/
/*OUTFILE= "C:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\price_changes_deepfryer_Apr2020.csv"*/
/*REPLACE;*/
/*run;*/
PROC EXPORT 
DATA=price_changes_sellertype
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\price_changes_deepfryer_July2025.csv"
REPLACE;
run;
proc print data = price_changes_sellertype(obs=10);
run;

/***count number of price changes by seller type and ASIN***/

proc sql;
create table num_pricechange_sellertype_asin as
select
seller_ID, 
seller_type,
ASIN,
count(distinct time1) as num_days,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
seller_ID, 
seller_type,
ASIN;
quit;
PROC EXPORT 
DATA=num_pricechange_sellertype_asin
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\num_pricechange_sellertype_asin_deepfryer_Apr2020.csv"
REPLACE;
run;

/***selecting the representative seller and ASIN per cluster with most number of price changes in the period***/

proc sort data = num_pricechange_sellertype_asin out= sorted_bymostchanges ;
by seller_type descending num_pricechange  ASIN;
run;

PROC EXPORT 
DATA=sorted_bymostchanges
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\sorted_bymostchanges_deepfryer_Apr2020.csv"
REPLACE;
run;
data new3psellers;
set sorted_bymostchanges;
where seller_type in ('Cluster 1', 'Cluster 2', 'Cluster 3', 'Cluster 4', 'Cluster 5');
run;
proc sort data = new3psellers nodupkey out= new3p_pricechange_vector;
by seller_type;
run;

data amazon;
set sorted_bymostchanges;
where seller_type in ('Amazon New') and ASIN IN ( 'B00NQ7QFGM', 'B00ANEKTTA', 'B005FYF7XQ', 'B01JCECNM2');
run;


data newandamazonsellers;
set new3p_pricechange_vector amazon;
run;

/**merging back to the price changes main file to get the trend for the chosen seller IDs and ASINs**/
proc sort data = price_changes_sellertype out= price_changes_sellertype1;
by seller_type seller_ID ASIN;
run;
proc sort data = newandamazonsellers out= newandamazonsellers1;
by seller_type seller_ID ASIN;
run;
data pricechangetrend_outcome;
merge newandamazonsellers1 (in=a) price_changes_sellertype1 (in=b);
by seller_type seller_ID ASIN;
if a and b;
run;
proc sort data = pricechangetrend_outcome (keep=time1) nodupkey out= final_outputvector;
by time1;
run;
%macro pricechange(a,b,c);
data pricechange&b. (keep = time1 pricechange_&b. price&b. pricediff_lowst_&b.);
set pricechangetrend_outcome;
where seller_type = &a. and ASIN = &c.;
rename pricechange = pricechange_&b. ;
rename price1 = price&b.;
rename pricediff_fromlowest = pricediff_lowst_&b.;
run;

data final_outputvector ;
merge final_outputvector (in=a) pricechange&b. (in=b);
by time1;
if a ;
if a and not b then pricechange_&b. = 0;
run;
%mend;
%pricechange(a='Amazon New', b = Amzn_tfal, c = 'B00NQ7QFGM'); 
%pricechange(a='Amazon New', b = Amzn_Hbeach, c = 'B00ANEKTTA'); 
%pricechange(a='Amazon New', b = Amzn_presto, c = 'B005FYF7XQ'); 
%pricechange(a='Amazon New', b = Amzn_csnart, c = 'B01JCECNM2'); 
%pricechange(a='Cluster 1', b = clust1_csnart, c = 'B01JCECNM2'); 
%pricechange(a='Cluster 2', b = clust2_tfal, c = 'B00NQ7QFGM'); 
%pricechange(a='Cluster 3', b = clust3_csnart, c = 'B01JCECNM2'); 
%pricechange(a='Cluster 4', b = clust4_secura, c = 'B007OZXVDM'); 
%pricechange(a='Cluster 5', b = clust5_tfal, c = 'B00NQ7QFGM' ); 
/*proc print data = pricechangetrend_outcome ;*/
/*var pricechange ASIN ;*/
/*where seller_type = 'Cluster 5' ;*/
/*run;*/
proc print data = final_outputvector ;
var pricechange_clust5_tfal;
run;

PROC EXPORT 
DATA=final_outputvector
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\Data Scraped\Amazon Others_merged\Cluster Analysis\pricechangetrend_deepfryer_July2025.csv"
REPLACE;
run;

/***CLUSTER MEMBERSHIP: NUMBER OF SELLERS IN A CLUSTER AT TIME T, BRANDS SOLD BY SELLER TYPES AT TIME T,
NUMBER OF PRICE CHANGES BY OWN AND CROSS BRANDS BY SELLER TYPES AT TIME (T-1), (T-2), (T-3), *******/
/*B000HEBAV2	Presto*/
/*B005FYF7XQ	Presto*/
/*B007OZXVDM	Secura*/
/*B008GS8PS2	Elite Platinum*/
/*B00ANEKTTA	Hamilton Beach*/
/*B00NQ7QFGM	T-fal*/
/*B01JCECNM2	Cuisinart*/


PROC SQL;
CREATE TABLE SELLER_offercharcs AS
SELECT
TIME1,
SELLER_TYPE,
COUNT(DISTINCT SELLER_ID) AS NUM_SELLERS,
COUNT(DISTINCT ASIN) AS NUM_SKUS,
sum(case when ASIN in ('B000HEBAV2','B005FYF7XQ') then 1 else 0 end) AS NUM_presto,
sum(case when ASIN = 'B007OZXVDM' then 1 else 0 end) as num_secura, 
sum(case when ASIN = 'B008GS8PS2' then 1 else 0 end) as num_EP,
sum(case when ASIN = 'B00ANEKTTA' then 1 else 0 end) as num_Hbeach, 
sum(case when ASIN = 'B00NQ7QFGM' then 1 else 0 end) as num_tfal, 
sum(case when ASIN = 'B01JCECNM2' then 1 else 0 end) as num_csnart 
from 
SELLER_TYPE_TREND12
group by
time1,
seller_type;
quit;

proc sort data = SELLER_offercharcs (keep = time1) nodupkey out= SELLER_offercharcs_base;
by time1;
run;

%macro seller_offercharacs(a,b);
data seller_offer&a.;
set SELLER_offercharcs;
where seller_type = &b.;
rename NUM_SELLERS = NUM_SELLERS&a.;
rename NUM_SKUS = NUM_SKUS&a.;
rename NUM_presto = NUM_presto&a.;
rename num_secura = num_secura&a.;
rename num_EP = num_EP&a.;
rename num_HBeach = num_HBeach&a.;
rename num_tfal = num_tfal&a.;
rename num_csnart = num_csnart&a.;
run;

proc sort data = seller_offer&a. (drop=  seller_type) out= sorted_seller_type;
by time1;
run;

data SELLER_offercharcs_base;
merge SELLER_offercharcs_base (in=a) sorted_seller_type (in=b);
by time1;
if a ;
if a and not b then do;
NUM_SELLERS&a. = 0;
NUM_SKUS&a. = 0;
NUM_presto&a.=0;
num_secura&a.= 0;
num_EP&a. = 0;
num_HBeach&a. = 0;
num_tfal&a. = 0;
num_csnart&a. = 0;
end;
run;
%mend;
%seller_offercharacs(a = used3p, b = '3P Old');
%seller_offercharacs(a = usedamzn, b = 'Amazon Old'); 
%seller_offercharacs(a = amzn, b = 'Amazon New');
%seller_offercharacs(a = clust1, b = 'Cluster 1'); 
%seller_offercharacs(a = clust2, b = 'Cluster 2'); 
%seller_offercharacs(a = clust3, b = 'Cluster 3');
%seller_offercharacs(a = clust4, b = 'Cluster 4');
%seller_offercharacs(a = clust5, b = 'Cluster 5'); 

/*****COMPETITOR PRICING INFORMATION*****/
/**ACCOUNTING FOR PRICE CHANGES IN EACH SELLER TYPE BY BRANDS****/

proc sql;
create table num_pricechange_sellertype_asin as
select
TIME1,
seller_type,
ASIN,
sum(case when pricechange ne 0 then 1 else 0 end) as num_pricechange
from
price_changes_sellertype
group by
time1,
seller_type,
ASIN;
quit;


proc sort data = num_pricechange_sellertype_asin (keep = time1)nodupkey out= numpricechange_sellerbrandtrend;
by time1;
run;
%macro seller_brand(a,b,c,d);
data num_pricechange_&d.&b.;
set num_pricechange_sellertype_asin;
where ASIN in &c. and seller_type = &a.;
rename num_pricechange = num_pricechange&b.&d.;
run;

proc sql;
create table num_pricechange1_&d.&b. as
select 
time1,
sum(num_pricechange&b.&d.) as num_pricechange1&b.&d.
from 
num_pricechange_&d.&b.
group by
time1;
quit;

proc sort data = num_pricechange1_&d.&b. NODUPKEY out= sorted;
by time1;
run;

data numpricechange_sellerbrandtrend;
merge numpricechange_sellerbrandtrend (in=a) sorted (in=b);
by time1;
if a ;
if a and not b then num_pricechange1&b.&d. = 0;
run;
%mend;

%seller_brand(a='3P Old',b= presto, c =('B01M0X67O0','B005FYF7XQ'), d= used3p);
%seller_brand(a='Amazon Old',b= presto, c=('B01M0X67O0','B005FYF7XQ'), d= usedamzn);
%seller_brand(a='Amazon New',b= presto, c=('B01M0X67O0','B005FYF7XQ'), d= amzn);
%seller_brand(a='Cluster 1',b= presto, c=('B01M0X67O0','B005FYF7XQ'), d= clust1);
%seller_brand(a='Cluster 2',b= presto, c=('B01M0X67O0','B005FYF7XQ'), d= clust2);
%seller_brand(a='Cluster 3',b= presto, c=('B01M0X67O0','B005FYF7XQ'), d= clust3);
%seller_brand(a='Cluster 4',b= presto, c=('B01M0X67O0','B005FYF7XQ'), d= clust4);
%seller_brand(a='Cluster 5',b= presto, c=('B01M0X67O0','B005FYF7XQ'), d= clust5);

%seller_brand(a='3P Old',b= secura, c =('B007OZXVDM'), d= used3p);
%seller_brand(a='Amazon Old',b= secura, c=('B007OZXVDM'), d= usedamzn);
%seller_brand(a='Amazon New',b= secura, c=('B007OZXVDM'), d= amzn);
%seller_brand(a='Cluster 1',b= secura, c=('B007OZXVDM'), d= clust1);
%seller_brand(a='Cluster 2',b= secura, c=('B007OZXVDM'), d= clust2);
%seller_brand(a='Cluster 3',b= secura, c=('B007OZXVDM'), d= clust3);
%seller_brand(a='Cluster 4',b= secura, c=('B007OZXVDM'), d= clust4);
%seller_brand(a='Cluster 5',b= secura, c=('B007OZXVDM'), d= clust5);

%seller_brand(a='3P Old',b= EP, c =('B008GS8PS2'), d= used3p);
%seller_brand(a='Amazon Old',b= EP, c=('B008GS8PS2'), d= usedamzn);
%seller_brand(a='Amazon New',b= EP, c=('B008GS8PS2'), d= amzn);
%seller_brand(a='Cluster 1',b= EP, c=('B008GS8PS2'), d= clust1);
%seller_brand(a='Cluster 2',b= EP, c=('B008GS8PS2'), d= clust2);
%seller_brand(a='Cluster 3',b= EP, c=('B008GS8PS2'), d= clust3);
%seller_brand(a='Cluster 4',b= EP, c=('B008GS8PS2'), d= clust4);
%seller_brand(a='Cluster 5',b= EP, c=('B008GS8PS2'), d= clust5);

%seller_brand(a='3P Old',b= Hbeach, c =('B00ANEKTTA'), d= used3p);
%seller_brand(a='Amazon Old',b= Hbeach, c=('B00ANEKTTA'), d= usedamzn);
%seller_brand(a='Amazon New',b= Hbeach, c=('B00ANEKTTA'), d= amzn);
%seller_brand(a='Cluster 1',b= Hbeach, c=('B00ANEKTTA'), d= clust1);
%seller_brand(a='Cluster 2',b= Hbeach, c=('B00ANEKTTA'), d= clust2);
%seller_brand(a='Cluster 3',b= Hbeach, c=('B00ANEKTTA'), d= clust3);
%seller_brand(a='Cluster 4',b= Hbeach, c=('B00ANEKTTA'), d= clust4);
%seller_brand(a='Cluster 5',b= Hbeach, c=('B00ANEKTTA'), d= clust5);

%seller_brand(a='3P Old',b= tfal, c =('B00NQ7QFGM'), d= used3p);
%seller_brand(a='Amazon Old',b= tfal, c=('B00NQ7QFGM'), d= usedamzn);
%seller_brand(a='Amazon New',b= tfal, c=('B00NQ7QFGM'), d= amzn);
%seller_brand(a='Cluster 1',b= tfal, c=('B00NQ7QFGM'), d= clust1);
%seller_brand(a='Cluster 2',b= tfal, c=('B00NQ7QFGM'), d= clust2);
%seller_brand(a='Cluster 3',b= tfal, c=('B00NQ7QFGM'), d= clust3);
%seller_brand(a='Cluster 4',b= tfal, c=('B00NQ7QFGM'), d= clust4);
%seller_brand(a='Cluster 5',b= tfal, c=('B00NQ7QFGM'), d= clust5);

%seller_brand(a='3P Old',b= csnart, c =('B01JCECNM2'), d= used3p);
%seller_brand(a='Amazon Old',b= csnart, c=('B01JCECNM2'), d= usedamzn);
%seller_brand(a='Amazon New',b= csnart, c=('B01JCECNM2'), d= amzn);
%seller_brand(a='Cluster 1',b= csnart, c=('B01JCECNM2'), d= clust1);
%seller_brand(a='Cluster 2',b= csnart, c=('B01JCECNM2'), d= clust2);
%seller_brand(a='Cluster 3',b= csnart, c=('B01JCECNM2'), d= clust3);
%seller_brand(a='Cluster 4',b= csnart, c=('B01JCECNM2'), d= clust4);
%seller_brand(a='Cluster 5',b= csnart, c=('B01JCECNM2'), d= clust5);
/****************************/
/*****SELLER ATTRIBUTES******/
/****************************/
/***EXTRACTING NUM OF UNIQUE ASINS AND NUM OF ASINS SOLD CONDITION FOR EACH SELLER***/
PROC SQL;
create table seller_asin as
select
time1,
seller_ID,
count(distinct ASIN) as unique_ASIN_sold,
count(distinct time1) as sell_freq
from
SELLER_TYPE_TREND12
group by
time1,
seller_ID
;
quit;
/**MERGING BACK TO THE MAIN DATA SET**/
PROC SORT DATA = SELLER_TYPE_TREND12 OUT= SELLER_TYPE_TREND4;
BY time1 seller_ID;
RUN;
PROC SORT DATA = seller_asin OUT= seller_asin1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND5;
MERGE SELLER_TYPE_TREND4 (IN=A) seller_asin1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;

/**creating cumulative sell freq over time***/
proc sort data = SELLER_TYPE_TREND5 (keep= time1 seller_ID sell_freq) nodupkey out=SELLER_TYPE_TREND6;
by seller_ID time1;
run;

data cum_sellfreq;
  set SELLER_TYPE_TREND6;
  by seller_ID ;
  if first.seller_ID then cum_sellerfreq = 0;
  cum_sellerfreq +sell_freq;
run;
/**merging back to main data set**/
PROC SORT DATA = SELLER_TYPE_TREND5 OUT= SELLER_TYPE_TREND7;
BY time1 seller_ID;
RUN;
PROC SORT DATA = cum_sellfreq OUT= cum_sellfreq1;
BY time1 seller_ID;
RUN;
DATA SELLER_TYPE_TREND8;
MERGE SELLER_TYPE_TREND7 (IN=A) cum_sellfreq1 (IN=B);
BY time1 seller_ID;
IF A AND B ;
RUN;

PROC SQL;
create table SELLER_CHAR as
select
time1,
seller_type,
SUM(FBA) AS NUM_FBA,
SUM(FREESHIP) AS NUM_FREESHIP,
SUM(CONDITION) AS NUM_NEWOFFERS,
sum(case when condition = 0 then 1 else 0 end) AS NUM_USEDOFFERS,
max(num_seller_Posrating) as max_perct_sellerrating,
mean(num_seller_Posrating) as mean_perct_sellerrating,
min(num_seller_Posrating) as min_perct_sellerrating,
max(num_seller_ratings) as max_num_seller_rating,
mean(num_seller_ratings) as mean_num_seller_rating,
min(num_seller_ratings) as min_num_seller_rating,
max(seller_rating) as max_seller_star_rating,
mean(seller_rating) as mean_seller_star_rating,
min(seller_rating) as min_seller_star_rating,
max(unique_ASIN_sold) as max_uniq_ASIN,
mean(unique_ASIN_sold) as mean_uniq_ASIN,
min(unique_ASIN_sold) as min_uniq_ASIN,
max(cum_sellerfreq) as max_cumsalefreq,
mean(cum_sellerfreq) as mean_cumsalefreq,
min(cum_sellerfreq) as min_cumsalefreq
from
SELLER_TYPE_TREND8
group by
time1,
seller_type
;
quit;

/**rename each seller char to individual columns for each seller cluster for model build, i.e., max_fba_clust1, min_fba_clust1 etc. ****/

proc sort data = SELLER_CHAR  (keep=time1) nodupkey out=seller_char_sorted;
by time1;
run;
data sellerchar_amzn;
set SELLER_CHAR;
where seller_type = 'Amazon New';
rename num_Freeship = Freeship_amzn;
rename max_cumsalefreq = cumsalefreq_amzn;
run;
data sellerchar_usedamzn;
set SELLER_CHAR;
where seller_type = 'Amazon Old';
rename num_Freeship = Freeship_usedamzn;
run;
data sellerchar_used3p;
set SELLER_CHAR;
where seller_type = '3P Old';
rename num_FBA = FBA_used3p;
rename num_Freeship = Freeship_used3p;
run;
proc sort data = sellerchar_amzn (keep = time1 Freeship_amzn cumsalefreq_amzn) out= sellerchar_amzn1;
by time1;
run;

proc sort data = sellerchar_usedamzn (keep = time1 Freeship_usedamzn  ) out= sellerchar_amznused1;
by time1;
run;
proc sort data = sellerchar_used3p (keep = time1 FBA_used3p Freeship_used3p  ) out= sellerchar_used3p1;
by time1;
run;
data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_amzn1 (in=b) sellerchar_amznused1 (in =c) sellerchar_used3p1 (in=d);
by time1;
if a ;
if  a and not b then do;
Freeship_amzn = 0;
cumsalefreq_amzn = 0;
end;
if a and not c then do;
Freeship_usedamzn = 0;
end;
if a and not d then do;
FBA_used3p = 0;
Freeship_used3p = 0;

end;
run;

%macro sellerchar(a,b);
data sellerchar_&b.;
set SELLER_CHAR;
where seller_type = &a.;
rename num_FBA = num_FBA_&b.;
rename num_Freeship = num_Freeship_&b.;
rename max_perct_sellerrating = max_perct_sellerrating_&b.;
rename mean_perct_sellerrating = mean_perct_sellerrating_&b.;
rename min_perct_sellerrating = min_perct_sellerrating_&b.;
rename max_num_seller_rating = max_num_seller_rating_&b.;
rename mean_num_seller_rating = mean_num_seller_rating_&b.;
rename min_num_seller_rating = min_num_seller_rating_&b.;
rename max_seller_star_rating = max_seller_star_rating_&b.;
rename mean_seller_star_rating = mean_seller_star_rating_&b.;
rename min_seller_star_rating = min_seller_star_rating_&b.;
rename max_uniq_ASIN = max_uniq_ASIN_&b.;
rename mean_uniq_ASIN = mean_uniq_ASIN_&b.;
rename min_uniq_ASIN = min_uniq_ASIN_&b.;
rename max_cumsalefreq = max_cumsalefreq_&b.;
rename mean_cumsalefreq = mean_cumsalefreq_&b.;
rename min_cumsalefreq = min_cumsalefreq_&b.;
run;

proc sort data = sellerchar_&b. (keep = time1 num_FBA_&b. num_Freeship_&b. max_perct_sellerrating_&b. 
mean_perct_sellerrating_&b. min_perct_sellerrating_&b. max_num_seller_rating_&b. mean_num_seller_rating_&b.
min_num_seller_rating_&b. max_seller_star_rating_&b. mean_seller_star_rating_&b. min_seller_star_rating_&b. 
max_uniq_ASIN_&b. mean_uniq_ASIN_&b. min_uniq_ASIN_&b. max_cumsalefreq_&b. mean_cumsalefreq_&b. 
min_cumsalefreq_&b.) out= sellerchar_sorted&b.;
by time1;
run;

data seller_char_sorted ;
merge seller_char_sorted (in=a) sellerchar_sorted&b. (in=b);
by time1;
if a ;
if a and not b then do;
num_FBA_&b. = 0;
num_Freeship_&b. = 0;
max_perct_sellerrating_&b. = 0;
mean_perct_sellerrating_&b. = 0;
min_perct_sellerrating_&b. = 0;
max_num_seller_rating_&b. = 0;
mean_num_seller_rating_&b. = 0;
min_num_seller_rating_&b. = 0;
max_seller_star_rating_&b. = 0;
mean_seller_star_rating_&b. = 0;
min_seller_star_rating_&b. = 0;
max_uniq_ASIN_&b. = 0;
mean_uniq_ASIN_&b. = 0;
min_uniq_ASIN_&b. = 0;
max_cumsalefreq_&b. = 0;
mean_cumsalefreq_&b. = 0;
min_cumsalefreq_&b. = 0;
end;
run;
%mend;
%sellerchar(a='Cluster 1', b = clust1); 
%sellerchar(a='Cluster 2', b = clust2); 
%sellerchar(a='Cluster 3', b = clust3); 
%sellerchar(a='Cluster 4', b = clust4); 
%sellerchar(a='Cluster 5', b = clust5);
proc contents data = focal_rival_prods27;
run;
/***********************************/
/*************BRAND ATTRIBUTES*****/
/*********************************/
/**SUMMARIZING PRODUCT CHAR FOR presto***/

PROC SQL;
create table PRODUCT_CHAR_presto as
select
time1,
MAX(Answered_Questions) as max_ans_questions_presto,
mean(Answered_Questions) as mean_ans_questions_presto,
min(Answered_Questions) as min_ans_questions_presto,
max(Buy_Box_Price1) as max_buyboxprice_presto,
mean(Buy_Box_Price1) as mean_buyboxprice_presto,
min(Buy_Box_Price1) as min_buyboxprice_presto,
max(nonbuybox3p_price) as max_nonbbox3pprice_presto,
mean(nonbuybox3p_price) as mean_nonbbox3pprice_presto,
min(nonbuybox3p_price) as min_nonbbox3pprice_presto,
max(Product_Star_Rate) as max_product_star_rate_presto,
mean(Product_Star_Rate) as mean_product_star_rate_presto,
min(Product_Star_Rate) as min_product_star_rate_presto,
max(Product_reviews) as max_Product_reviews_presto,
mean(Product_reviews) as mean_Product_reviews_presto,
min(Product_reviews) as min_Product_reviews_presto,
max(Sales_Rank_Own_Categ) as max_salesrank_categ_presto,
mean(Sales_Rank_Own_Categ) as mean_salesrank_categ_presto,
min(Sales_Rank_Own_Categ) as min_salesrank_categ_presto,
max(Sales_Rank_Sub_categ) as max_salesrank_subcateg_presto,
mean(Sales_Rank_Sub_categ) as mean_salesrank_subcateg_presto,
min(Sales_Rank_Sub_categ) as min_salesrank_subcateg_presto
from
focal_rival_prods27
WHERE ASIN IN ('B01M0X67O0','B005FYF7XQ')
group by
time1;
quit;

/**MERGING BACK TO THE SELLER CHARACTERISTICS DATA***/
DATA seller_char_sorted;
MERGE seller_char_sorted (IN=A) PRODUCT_CHAR_presto (IN=B) SELLER_offercharcs_base (in=c);
BY TIME1;
IF A ;
IF A AND NOT B THEN DO;
max_ans_questions_presto =0;
mean_ans_questions_presto =0;
min_ans_questions_presto =0;
max_buyboxprice_presto =0;
mean_buyboxprice_presto =0;
min_buyboxprice_presto =0;
max_nonbbox3pprice_presto =0;
mean_nonbbox3pprice_presto =0;
min_nonbbox3pprice_presto =0;
max_product_star_rate_presto =0;
mean_product_star_rate_presto =0;
min_product_star_rate_presto =0;
max_Product_reviews_presto =0;
mean_Product_reviews_presto =0;
min_Product_reviews_presto =0;
max_salesrank_categ_presto =0;
mean_salesrank_categ_presto =0;
min_salesrank_categ_presto =0;
max_salesrank_subcateg_presto =0;
mean_salesrank_subcateg_presto =0;
min_salesrank_subcateg_presto =0;
END;
RUN;


proc sort data = seller_char_sorted out= ALL_char_sorted ;
by time1;
run;
%macro prodchar(a,b);
data prodchar_&b.;
set focal_rival_prods27;
where ASIN = &a.;
rename Answered_Questions = Answered_Questions&b. ;
rename Buy_Box_Price1 = buyboxprice_&b.;
rename nonbuybox3p_price = nonbbox3pprice_&b.;
rename Product_Star_Rate = product_star_&b.;
rename Product_reviews = Product_reviews_&b.;
rename Sales_Rank_Own_Categ = Sales_Rank_Own_Categ_&b.;
rename Sales_Rank_Sub_categ = Sales_Rank_Sub_categ_&b.;
run;

proc sort data = prodchar_&b. (keep = time1  Answered_Questions&b. buyboxprice_&b. nonbbox3pprice_&b. product_star_&b. 
Product_reviews_&b. Sales_Rank_Own_Categ_&b. Sales_Rank_Sub_categ_&b.) NODUPKEY out= prodchar_sorted&b.;
by time1;
run;

data ALL_char_sorted ;
merge ALL_char_sorted (in=a) prodchar_sorted&b. (in=b);
by time1;
if a;
IF A AND NOT B THEN DO;
Answered_Questions&b. = 0;
buyboxprice_&b. = 0;
nonbbox3pprice_&b. =0;
product_star_&b. = 0;
Product_reviews_&b. = 0;
Sales_Rank_Own_Categ_&b. = 0;
Sales_Rank_Sub_categ_&b. = 0;
END;
run;
%mend;
%prodchar(a= 'B007OZXVDM', b = secura); 
%prodchar(a= 'B008GS8PS2', b = EP);
%prodchar(a= 'B00ANEKTTA', b = Hbeach);
%prodchar(a= 'B00NQ7QFGM', b = tfal);
%prodchar(a= 'B01JCECNM2', b = csnart);
proc print data = ALL_char_sorted;
var nonbbox3pprice_tfal;
run;


/**MERGING WITH BRAND SELLER PRICE CHANGE TREND**/
DATA ALL_COVARIATES ;
MERGE ALL_char_sorted (IN = A) numpricechange_sellerbrandtrend (IN=B) ;
BY TIME1;
IF A ;
RUN;
proc contents data = ALL_COVARIATES;
run;
/*****merging with final outcome vector****/

proc sort data = final_outputvector ;
by time1;
run;

data merge_finaldata;
merge final_outputvector (in=a) ALL_COVARIATES (in=b);
by time1;
if a;
rename pricechange_Amzn_Hbeach = Y_amzn_hbeach;
rename pricechange_Amzn_csnart = Y_amzn_csnart;
rename pricechange_Amzn_presto = Y_amzn_presto;
rename pricechange_Amzn_tfal = Y_amzn_tfal;
rename pricechange_clust1_csnart = Y_clust1_csnart;
rename pricechange_clust2_tfal = Y_clust2_tfal;
rename pricechange_clust3_csnart = Y_clust3_csnart;
rename pricechange_clust4_secura = Y_clust4_secura;
rename pricechange_clust5_tfal = Y_clust5_tfal;
run;
proc print data = merge_finaldata (obs=100);
var pricediff_lowst_clust5_tfal;
run;

proc contents data = merge_finaldata;
run;
proc sort data = merge_finaldata NODUPREC out=merge_finaldata1 ;
by  TIME1 ;
run;


proc expand data=merge_finaldata1 out=merge_finaldata2 method = none; 
convert	Y_amzn_csnart	=	Y_amzn_csnart_lag1	/transformout=(lag 1);
convert	Y_amzn_csnart	=	Y_amzn_csnart_lag2	/transformout=(lag 2);
convert	Y_amzn_csnart	=	Y_amzn_csnart_lag3	/transformout=(lag 3);
convert	Y_amzn_hbeach	=	Y_amzn_hbeach_lag1	/transformout=(lag 1);
convert	Y_amzn_hbeach	=	Y_amzn_hbeach_lag2	/transformout=(lag 2);
convert	Y_amzn_hbeach	=	Y_amzn_hbeach_lag3	/transformout=(lag 3);
convert	Y_amzn_presto	=	Y_amzn_presto_lag1	/transformout=(lag 1);
convert	Y_amzn_presto	=	Y_amzn_presto_lag2	/transformout=(lag 2);
convert	Y_amzn_presto	=	Y_amzn_presto_lag3	/transformout=(lag 3);
convert	Y_amzn_tfal	=	Y_amzn_tfal_lag1	/transformout=(lag 1);
convert	Y_amzn_tfal	=	Y_amzn_tfal_lag2	/transformout=(lag 2);
convert	Y_amzn_tfal	=	Y_amzn_tfal_lag3	/transformout=(lag 3);
convert	Y_clust1_csnart	=	Y_clust1_csnart_lag1	/transformout=(lag 1);
convert	Y_clust1_csnart	=	Y_clust1_csnart_lag2	/transformout=(lag 2);
convert	Y_clust1_csnart	=	Y_clust1_csnart_lag3	/transformout=(lag 3);
convert	Y_clust2_tfal	=	Y_clust2_tfal_lag1	/transformout=(lag 1);
convert	Y_clust2_tfal	=	Y_clust2_tfal_lag2	/transformout=(lag 2);
convert	Y_clust2_tfal	=	Y_clust2_tfal_lag3	/transformout=(lag 3);
convert	Y_clust3_csnart	=	Y_clust3_csnart_lag1	/transformout=(lag 1);
convert	Y_clust3_csnart	=	Y_clust3_csnart_lag2	/transformout=(lag 2);
convert	Y_clust3_csnart	=	Y_clust3_csnart_lag3	/transformout=(lag 3);
convert	Y_clust4_secura	=	Y_clust4_secura_lag1	/transformout=(lag 1);
convert	Y_clust4_secura	=	Y_clust4_secura_lag2	/transformout=(lag 2);
convert	Y_clust4_secura	=	Y_clust4_secura_lag3	/transformout=(lag 3);
convert	Y_clust5_tfal	=	Y_clust5_tfal_lag1	/transformout=(lag 1);
convert	Y_clust5_tfal	=	Y_clust5_tfal_lag2	/transformout=(lag 2);
convert	Y_clust5_tfal	=	Y_clust5_tfal_lag3	/transformout=(lag 3);
convert	Answered_QuestionsEP	=	Answered_QuestionsEP_lag1	/transformout=(lag 1);
convert	Answered_QuestionsHbeach	=	Answered_QuestionsHbeach_lag1	/transformout=(lag 1);
convert	Answered_Questionscsnart	=	Answered_Questionscsnart_lag1	/transformout=(lag 1);
convert	Answered_Questionssecura	=	Answered_Questionssecura_lag1	/transformout=(lag 1);
convert	Answered_Questionstfal	=	Answered_Questionstfal_lag1	/transformout=(lag 1);
convert	FBA_used3p	=	FBA_used3p_lag1	/transformout=(lag 1);
convert	Freeship_amzn	=	Freeship_amzn_lag1	/transformout=(lag 1);
convert	Freeship_used3p	=	Freeship_used3p_lag1	/transformout=(lag 1);
convert	Freeship_usedamzn	=	Freeship_usedamzn_lag1	/transformout=(lag 1);
convert nonbbox3pprice_EP =   nonbbox3pprice_EP_lag  /transformout=(lag 1);
convert nonbbox3pprice_Hbeach =   nonbbox3pprice_Hbeach_lag  /transformout=(lag 1);
convert nonbbox3pprice_csnart =   nonbbox3pprice_csnart_lag  /transformout=(lag 1);
convert nonbbox3pprice_secura =   nonbbox3pprice_secura_lag  /transformout=(lag 1);
convert nonbbox3pprice_tfal =  nonbbox3pprice_tfal_lag /transformout=(lag 1);
convert	NUM_SELLERSamzn	=	NUM_SELLERSamzn_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust1	=	NUM_SELLERSclust1_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust2	=	NUM_SELLERSclust2_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust3	=	NUM_SELLERSclust3_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust4	=	NUM_SELLERSclust4_lag1	/transformout=(lag 1);
convert	NUM_SELLERSclust5	=	NUM_SELLERSclust5_lag1	/transformout=(lag 1);
convert	NUM_SELLERSused3p	=	NUM_SELLERSused3p_lag1	/transformout=(lag 1);
convert	NUM_SELLERSusedamzn	=	NUM_SELLERSusedamzn_lag1	/transformout=(lag 1);
convert	NUM_SKUSamzn	=	NUM_SKUSamzn_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust1	=	NUM_SKUSclust1_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust2	=	NUM_SKUSclust2_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust3	=	NUM_SKUSclust3_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust4	=	NUM_SKUSclust4_lag1	/transformout=(lag 1);
convert	NUM_SKUSclust5	=	NUM_SKUSclust5_lag1	/transformout=(lag 1);
convert	NUM_SKUSused3p	=	NUM_SKUSused3p_lag1	/transformout=(lag 1);
convert	NUM_SKUSusedamzn	=	NUM_SKUSusedamzn_lag1	/transformout=(lag 1);
convert	NUM_prestoamzn	=	NUM_prestoamzn_lag1	/transformout=(lag 1);
convert	NUM_prestoclust1	=	NUM_prestoclust1_lag1	/transformout=(lag 1);
convert	NUM_prestoclust2	=	NUM_prestoclust2_lag1	/transformout=(lag 1);
convert	NUM_prestoclust3	=	NUM_prestoclust3_lag1	/transformout=(lag 1);
convert	NUM_prestoclust4	=	NUM_prestoclust4_lag1	/transformout=(lag 1);
convert	NUM_prestoclust5	=	NUM_prestoclust5_lag1	/transformout=(lag 1);
convert	NUM_prestoused3p	=	NUM_prestoused3p_lag1	/transformout=(lag 1);
convert	NUM_prestousedamzn	=	NUM_prestousedamzn_lag1	/transformout=(lag 1);
convert	Product_reviews_EP	=	Product_reviews_EP_lag1	/transformout=(lag 1);
convert	Product_reviews_Hbeach	=	Product_reviews_Hbeach_lag1	/transformout=(lag 1);
convert	Product_reviews_csnart	=	Product_reviews_csnart_lag1	/transformout=(lag 1);
convert	Product_reviews_secura	=	Product_reviews_secura_lag1	/transformout=(lag 1);
convert	Product_reviews_tfal	=	Product_reviews_tfal_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_EP	=	salerank_EP_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_Hbeach	=	salerank_Hbeach_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_csnart	=	salerank_csnart_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_secura	=	salerank_secura_lag1	/transformout=(lag 1);
convert	Sales_Rank_Own_Categ_tfal	=	salerank_tfal_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_EP	=	saleranksubcat_EP_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_Hbeach	=	saleranksubcat_Hbeach_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_csnart	=	saleranksubcat_csnart_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_secura	=	saleranksubcat_secura_lag1	/transformout=(lag 1);
convert	Sales_Rank_Sub_categ_tfal	=	saleranksubcat_tfal_lag1	/transformout=(lag 1);
convert	buyboxprice_EP	=	buyboxprice_EP_lag1	/transformout=(lag 1);
convert	buyboxprice_Hbeach	=	buyboxprice_Hbeach_lag1	/transformout=(lag 1);
convert	buyboxprice_csnart	=	buyboxprice_csnart_lag1	/transformout=(lag 1);
convert	buyboxprice_secura	=	buyboxprice_secura_lag1	/transformout=(lag 1);
convert	buyboxprice_tfal	=	buyboxprice_tfal_lag1	/transformout=(lag 1);
convert	cumsalefreq_amzn	=	cumsalefreq_amzn_lag1	/transformout=(lag 1);
convert	max_Product_reviews_presto	=	max_Product_reviews_presto_lag1	/transformout=(lag 1);
convert	max_ans_questions_presto	=	max_ans_questions_presto_lag1	/transformout=(lag 1);
convert	max_buyboxprice_presto	=	max_buyboxprice_presto_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust1	=	max_cumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust2	=	max_cumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust3	=	max_cumsalefreq_clust3_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust4	=	max_cumsalefreq_clust4_lag1	/transformout=(lag 1);
convert	max_cumsalefreq_clust5	=	max_cumsalefreq_clust5_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust1	=	maxnumsellerrate_clust1_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust2	=	maxnumsellerate_clust2_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust3	=	maxnumsellerate_clust3_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust4	=	maxnumsellerate_clust4_lag1	/transformout=(lag 1);
convert	max_num_seller_rating_clust5	=	maxnumsellerate_clust5_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust1	=	maxpercsellerate_clust1_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust2	=	maxpercsellerate_clust2_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust3	=	maxpercsellerate_clust3_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust4	=	maxpercsellerate_clust4_lag1	/transformout=(lag 1);
convert	max_perct_sellerrating_clust5	=	maxpercsellerate_clust5_lag1	/transformout=(lag 1);
convert	max_product_star_rate_presto	=	maxprodstar_presto_lag1	/transformout=(lag 1);
convert	max_salesrank_categ_presto	=	maxsalerank_presto_lag1	/transformout=(lag 1);
convert	max_salesrank_subcateg_presto	=	maxsaleranksubcat_presto_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust1	=	maxsellerstar_clust1_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust2	=	maxsellerstar_clust2_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust3	=	maxsellerstar_clust3_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust4	=	maxsellerstar_clust4_lag1	/transformout=(lag 1);
convert	max_seller_star_rating_clust5	=	maxsellerstar_clust5_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust1	=	max_uniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust2	=	max_uniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust3	=	max_uniq_ASIN_clust3_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust4	=	max_uniq_ASIN_clust4_lag1	/transformout=(lag 1);
convert	max_uniq_ASIN_clust5	=	max_uniq_ASIN_clust5_lag1	/transformout=(lag 1);
convert	mean_Product_reviews_presto	=	mean_Product_reviews_presto_lag1	/transformout=(lag 1);
convert	mean_ans_questions_presto	=	mean_ans_questions_presto_lag1	/transformout=(lag 1);
convert	mean_buyboxprice_presto	=	mean_buyboxprice_presto_lag1	/transformout=(lag 1);
convert mean_nonbbox3pprice_presto = mean_nonbbox3p_presto_lag      /transformout=(lag 1);
convert	mean_cumsalefreq_clust1	=	mean_cumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust2	=	mean_cumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust3	=	mean_cumsalefreq_clust3_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust4	=	mean_cumsalefreq_clust4_lag1	/transformout=(lag 1);
convert	mean_cumsalefreq_clust5	=	mean_cumsalefreq_clust5_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust1	=	meannumsellerate_clust1_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust2	=	meannumsellerate_clust2_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust3	=	meannumsellerate_clust3_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust4	=	meannumsellerate_clust4_lag1	/transformout=(lag 1);
convert	mean_num_seller_rating_clust5	=	meannumsellerate_clust5_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust1	=	meanpercsellerate_clust1_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust2	=	meanpercsellerate_clust2_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust3	=	meanpercsellerate_clust3_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust4	=	meanpercsellerate_clust4_lag1	/transformout=(lag 1);
convert	mean_perct_sellerrating_clust5	=	meanpercsellerate_clust5_lag1	/transformout=(lag 1);
convert	mean_product_star_rate_presto	=	meanprodstar_presto_lag1	/transformout=(lag 1);
convert	mean_salesrank_categ_presto	=	meansalerank_presto_lag1	/transformout=(lag 1);
convert	mean_salesrank_subcateg_presto	=	meansaleranksubcat_presto_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust1	=	meansellerstar_clust1_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust2	=	meansellerstar_clust2_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust3	=	meansellerstar_clust3_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust4	=	meansellerstar_clust4_lag1	/transformout=(lag 1);
convert	mean_seller_star_rating_clust5	=	meansellerstar_clust5_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust1	=	mean_uniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust2	=	mean_uniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust3	=	mean_uniq_ASIN_clust3_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust4	=	mean_uniq_ASIN_clust4_lag1	/transformout=(lag 1);
convert	mean_uniq_ASIN_clust5	=	mean_uniq_ASIN_clust5_lag1	/transformout=(lag 1);
convert	min_Product_reviews_presto	=	min_Product_reviews_presto_lag1	/transformout=(lag 1);
convert	min_ans_questions_presto	=	min_ans_questions_presto_lag1	/transformout=(lag 1);
convert	min_buyboxprice_presto	=	min_buyboxprice_presto_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust1	=	min_cumsalefreq_clust1_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust2	=	min_cumsalefreq_clust2_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust3	=	min_cumsalefreq_clust3_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust4	=	min_cumsalefreq_clust4_lag1	/transformout=(lag 1);
convert	min_cumsalefreq_clust5	=	min_cumsalefreq_clust5_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust1	=	minnumsellerate_clust1_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust2	=	minnumsellerate_clust2_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust3	=	minnumsellerate_clust3_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust4	=	minnumsellerate_clust4_lag1	/transformout=(lag 1);
convert	min_num_seller_rating_clust5	=	minnumsellerate_clust5_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust1	=	minpercsellerate_clust1_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust2	=	minpercsellerate_clust2_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust3	=	minpercsellerate_clust3_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust4	=	minpercsellerate_clust4_lag1	/transformout=(lag 1);
convert	min_perct_sellerrating_clust5	=	minpercsellerate_clust5_lag1	/transformout=(lag 1);
convert	min_product_star_rate_presto	=	minproductstar_presto_lag1	/transformout=(lag 1);
convert	min_salesrank_categ_presto	=	minsalerank_presto_lag1	/transformout=(lag 1);
convert	min_salesrank_subcateg_presto	=	minsaleranksubcat_presto_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust1	=	minsellerstar_clust1_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust2	=	minsellerstar_clust2_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust3	=	minsellerstar_clust3_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust4	=	minsellerstar_clust4_lag1	/transformout=(lag 1);
convert	min_seller_star_rating_clust5	=	minsellerstar_clust5_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust1	=	min_uniq_ASIN_clust1_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust2	=	min_uniq_ASIN_clust2_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust3	=	min_uniq_ASIN_clust3_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust4	=	min_uniq_ASIN_clust4_lag1	/transformout=(lag 1);
convert	min_uniq_ASIN_clust5	=	min_uniq_ASIN_clust5_lag1	/transformout=(lag 1);
convert	num_EPamzn	=	num_EPamzn_lag1	/transformout=(lag 1);
convert	num_EPclust1	=	num_EPclust1_lag1	/transformout=(lag 1);
convert	num_EPclust2	=	num_EPclust2_lag1	/transformout=(lag 1);
convert	num_EPclust3	=	num_EPclust3_lag1	/transformout=(lag 1);
convert	num_EPclust4	=	num_EPclust4_lag1	/transformout=(lag 1);
convert	num_EPclust5	=	num_EPclust5_lag1	/transformout=(lag 1);
convert	num_EPused3p	=	num_EPused3p_lag1	/transformout=(lag 1);
convert	num_EPusedamzn	=	num_EPusedamzn_lag1	/transformout=(lag 1);
convert	num_FBA_clust1	=	num_FBA_clust1_lag1	/transformout=(lag 1);
convert	num_FBA_clust2	=	num_FBA_clust2_lag1	/transformout=(lag 1);
convert	num_FBA_clust3	=	num_FBA_clust3_lag1	/transformout=(lag 1);
convert	num_FBA_clust4	=	num_FBA_clust4_lag1	/transformout=(lag 1);
convert	num_FBA_clust5	=	num_FBA_clust5_lag1	/transformout=(lag 1);
convert	num_Freeship_clust1	=	num_Freeship_clust1_lag1	/transformout=(lag 1);
convert	num_Freeship_clust2	=	num_Freeship_clust2_lag1	/transformout=(lag 1);
convert	num_Freeship_clust3	=	num_Freeship_clust3_lag1	/transformout=(lag 1);
convert	num_Freeship_clust4	=	num_Freeship_clust4_lag1	/transformout=(lag 1);
convert	num_Freeship_clust5	=	num_Freeship_clust5_lag1	/transformout=(lag 1);
convert	num_HBeachamzn	=	num_HBeachamzn_lag1	/transformout=(lag 1);
convert	num_HBeachclust1	=	num_HBeachclust1_lag1	/transformout=(lag 1);
convert	num_HBeachclust2	=	num_HBeachclust2_lag1	/transformout=(lag 1);
convert	num_HBeachclust3	=	num_HBeachclust3_lag1	/transformout=(lag 1);
convert	num_HBeachclust4	=	num_HBeachclust4_lag1	/transformout=(lag 1);
convert	num_HBeachclust5	=	num_HBeachclust5_lag1	/transformout=(lag 1);
convert	num_HBeachused3p	=	num_HBeachused3p_lag1	/transformout=(lag 1);
convert	num_HBeachusedamzn	=	num_HBeachusedamzn_lag1	/transformout=(lag 1);
convert	num_csnartamzn	=	num_csnartamzn_lag1	/transformout=(lag 1);
convert	num_csnartclust1	=	num_csnartclust1_lag1	/transformout=(lag 1);
convert	num_csnartclust2	=	num_csnartclust2_lag1	/transformout=(lag 1);
convert	num_csnartclust3	=	num_csnartclust3_lag1	/transformout=(lag 1);
convert	num_csnartclust4	=	num_csnartclust4_lag1	/transformout=(lag 1);
convert	num_csnartclust5	=	num_csnartclust5_lag1	/transformout=(lag 1);
convert	num_csnartused3p	=	num_csnartused3p_lag1	/transformout=(lag 1);
convert	num_csnartusedamzn	=	num_csnartusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1EPamzn	=	pricechangeEPamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1EPclust1	=	pricechangeEPclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1EPclust2	=	pricechangeEPclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1EPclust3	=	pricechangeEPclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1EPclust4	=	pricechangeEPclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1EPclust5	=	pricechangeEPclust5_lag1	/transformout=(lag 1);
convert	num_pricechange1EPused3p	=	pricechangeEPused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1EPusedamzn	=	pricechangeEPusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachamzn	=	pricechangeHbeachamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachclust1	=	pricechangeHbeachclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachclust2	=	pricechangeHbeachclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachclust3	=	pricechangeHbeachclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachclust4	=	pricechangeHbeachclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachclust5	=	pricechangeHbeachclust5_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachused3p	=	pricechangeHbeachused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1Hbeachusedamzn	=	pricechangeHbeachusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartamzn	=	pricechangecsnartamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartclust1	=	pricechangecsnartclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartclust2	=	pricechangecsnartclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartclust3	=	pricechangecsnartclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartclust4	=	pricechangecsnartclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartclust5	=	pricechangecsnartclust5_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartused3p	=	pricechangecsnartused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1csnartusedamzn	=	pricechangecsnartusedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1prestoamzn	=	pricechangeprestoamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1prestoclust1	=	pricechangeprestoclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1prestoclust2	=	pricechangeprestoclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1prestoclust3	=	pricechangeprestoclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1prestoclust4	=	pricechangeprestoclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1prestoclust5	=	pricechangeprestoclust5_lag1	/transformout=(lag 1);
convert	num_pricechange1prestoused3p	=	pricechangeprestoused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1prestousedamzn	=	pricechangeprestousedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1securaamzn	=	pricechangesecuraamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1securaclust1	=	pricechangesecuraclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1securaclust2	=	pricechangesecuraclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1securaclust3	=	pricechangesecuraclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1securaclust4	=	pricechangesecuraclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1securaclust5	=	pricechangesecuraclust5_lag1	/transformout=(lag 1);
convert	num_pricechange1securaused3p	=	pricechangesecuraused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1securausedamzn	=	pricechangesecurausedamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalamzn	=	pricechangetfalamzn_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalclust1	=	pricechangetfalclust1_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalclust2	=	pricechangetfalclust2_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalclust3	=	pricechangetfalclust3_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalclust4	=	pricechangetfalclust4_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalclust5	=	pricechangetfalclust5_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalused3p	=	pricechangetfalused3p_lag1	/transformout=(lag 1);
convert	num_pricechange1tfalusedamzn	=	pricechangetfalusedamzn_lag1	/transformout=(lag 1);
convert	num_securaamzn	=	num_securaamzn_lag1	/transformout=(lag 1);
convert	num_securaclust1	=	num_securaclust1_lag1	/transformout=(lag 1);
convert	num_securaclust2	=	num_securaclust2_lag1	/transformout=(lag 1);
convert	num_securaclust3	=	num_securaclust3_lag1	/transformout=(lag 1);
convert	num_securaclust4	=	num_securaclust4_lag1	/transformout=(lag 1);
convert	num_securaclust5	=	num_securaclust5_lag1	/transformout=(lag 1);
convert	num_securaused3p	=	num_securaused3p_lag1	/transformout=(lag 1);
convert	num_securausedamzn	=	num_securausedamzn_lag1	/transformout=(lag 1);
convert	num_tfalamzn	=	num_tfalamzn_lag1	/transformout=(lag 1);
convert	num_tfalclust1	=	num_tfalclust1_lag1	/transformout=(lag 1);
convert	num_tfalclust2	=	num_tfalclust2_lag1	/transformout=(lag 1);
convert	num_tfalclust3	=	num_tfalclust3_lag1	/transformout=(lag 1);
convert	num_tfalclust4	=	num_tfalclust4_lag1	/transformout=(lag 1);
convert	num_tfalclust5	=	num_tfalclust5_lag1	/transformout=(lag 1);
convert	num_tfalused3p	=	num_tfalused3p_lag1	/transformout=(lag 1);
convert	num_tfalusedamzn	=	num_tfalusedamzn_lag1	/transformout=(lag 1);
convert	priceAmzn_Hbeach	=	priceAmzn_Hbeach_lag	/transformout=(lag 1);
convert	priceAmzn_csnart	=	priceAmzn_csnart_lag	/transformout=(lag 1);
convert	priceAmzn_presto	=	priceAmzn_presto_lag	/transformout=(lag 1);
convert	priceAmzn_tfal	=	priceAmzn_tfal_lag	/transformout=(lag 1);
convert	priceclust1_csnart	=	priceclust1_csnart_lag	/transformout=(lag 1);
convert	priceclust2_tfal	=	priceclust2_tfal_lag	/transformout=(lag 1);
convert	priceclust3_csnart	=	priceclust3_csnart_lag	/transformout=(lag 1);
convert	priceclust4_secura	=	priceclust4_secura_lag	/transformout=(lag 1);
convert	priceclust5_tfal	=	priceclust5_tfal_lag	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_Hbeach	=	pricedifflowst_AmznHbeach_lag	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_csnart	=	pricedifflowst_Amzncsnart_lag	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_presto	=	pricedifflowst_Amznpresto_lag	/transformout=(lag 1);
convert	pricediff_lowst_Amzn_tfal	=	pricedifflowst_Amzntfal_lag	/transformout=(lag 1);
convert	pricediff_lowst_clust1_csnart	=	pricedifflowst_clust1csnart_lag	/transformout=(lag 1);
convert	pricediff_lowst_clust2_tfal	=	pricedifflowst_clust2tfal_lag	/transformout=(lag 1);
convert	pricediff_lowst_clust3_csnart	=	pricedifflowst_clust3csn_lag	/transformout=(lag 1);
convert	pricediff_lowst_clust4_secura	=	pricedifflowst_clust4sec_lag	/transformout=(lag 1);
convert	pricediff_lowst_clust5_tfal	=	pricedifflowst_clust5tfal_lag	/transformout=(lag 1);
convert	product_star_EP	=	productstar_EP_lag1	/transformout=(lag 1);
convert	product_star_Hbeach	=	productstar_Hbeach_lag1	/transformout=(lag 1);
convert	product_star_csnart	=	productstar_csnart_lag1	/transformout=(lag 1);
convert	product_star_secura	=	productstar_secura_lag1	/transformout=(lag 1);
convert	product_star_tfal	=	productstar_tfal_lag1	/transformout=(lag 1);

run;
/*/******REMOVING REDUNDANT VARIABLES***/*/
/**/
/*DATA MVRF_MODEL (DROP = Answered_QuestionsEP*/
/*Answered_QuestionsHbeach*/
/*Answered_Questionscsnart*/
/*Answered_Questionssecura*/
/*Answered_Questionstfal*/
/*FBA_used3p*/
/*Freeship_amzn*/
/*Freeship_used3p*/
/*Freeship_usedamzn*/
/*NUM_SELLERSamzn*/
/*NUM_SELLERSclust1*/
/*NUM_SELLERSclust2*/
/*NUM_SELLERSclust3*/
/*NUM_SELLERSclust4*/
/*NUM_SELLERSclust5*/
/*NUM_SELLERSused3p*/
/*NUM_SELLERSusedamzn*/
/*NUM_SKUSamzn*/
/*NUM_SKUSclust1*/
/*NUM_SKUSclust2*/
/*NUM_SKUSclust3*/
/*NUM_SKUSclust4*/
/*NUM_SKUSclust5*/
/*NUM_SKUSused3p*/
/*NUM_SKUSusedamzn*/
/*NUM_prestoamzn*/
/*NUM_prestoclust1*/
/*NUM_prestoclust2*/
/*NUM_prestoclust3*/
/*NUM_prestoclust4*/
/*NUM_prestoclust5*/
/*NUM_prestoused3p*/
/*NUM_prestousedamzn*/
/*Product_reviews_EP*/
/*Product_reviews_Hbeach*/
/*Product_reviews_csnart*/
/*Product_reviews_secura*/
/*Product_reviews_tfal*/
/*Sales_Rank_Own_Categ_EP*/
/*Sales_Rank_Own_Categ_Hbeach*/
/*Sales_Rank_Own_Categ_csnart*/
/*Sales_Rank_Own_Categ_secura*/
/*Sales_Rank_Own_Categ_tfal*/
/*Sales_Rank_Sub_categ_EP*/
/*Sales_Rank_Sub_categ_Hbeach*/
/*Sales_Rank_Sub_categ_csnart*/
/*Sales_Rank_Sub_categ_secura*/
/*Sales_Rank_Sub_categ_tfal*/
/*buyboxprice_EP*/
/*buyboxprice_Hbeach*/
/*buyboxprice_csnart*/
/*buyboxprice_secura*/
/*buyboxprice_tfal*/
/*cumsalefreq_amzn*/
/*max_Product_reviews_presto*/
/*max_ans_questions_presto*/
/*max_buyboxprice_presto*/
/*max_cumsalefreq_clust1*/
/*max_cumsalefreq_clust2*/
/*max_cumsalefreq_clust3*/
/*max_cumsalefreq_clust4*/
/*max_cumsalefreq_clust5*/
/*max_num_seller_rating_clust1*/
/*max_num_seller_rating_clust2*/
/*max_num_seller_rating_clust3*/
/*max_num_seller_rating_clust4*/
/*max_num_seller_rating_clust5*/
/*max_perct_sellerrating_clust1*/
/*max_perct_sellerrating_clust2*/
/*max_perct_sellerrating_clust3*/
/*max_perct_sellerrating_clust4*/
/*max_perct_sellerrating_clust5*/
/*max_product_star_rate_presto*/
/*max_salesrank_categ_presto*/
/*max_salesrank_subcateg_presto*/
/*max_seller_star_rating_clust1*/
/*max_seller_star_rating_clust2*/
/*max_seller_star_rating_clust3*/
/*max_seller_star_rating_clust4*/
/*max_seller_star_rating_clust5*/
/*max_uniq_ASIN_clust1*/
/*max_uniq_ASIN_clust2*/
/*max_uniq_ASIN_clust3*/
/*max_uniq_ASIN_clust4*/
/*max_uniq_ASIN_clust5*/
/*mean_Product_reviews_presto*/
/*mean_ans_questions_presto*/
/*mean_buyboxprice_presto*/
/*mean_cumsalefreq_clust1*/
/*mean_cumsalefreq_clust2*/
/*mean_cumsalefreq_clust3*/
/*mean_cumsalefreq_clust4*/
/*mean_cumsalefreq_clust5*/
/*mean_num_seller_rating_clust1*/
/*mean_num_seller_rating_clust2*/
/*mean_num_seller_rating_clust3*/
/*mean_num_seller_rating_clust4*/
/*mean_num_seller_rating_clust5*/
/*mean_perct_sellerrating_clust1*/
/*mean_perct_sellerrating_clust2*/
/*mean_perct_sellerrating_clust3*/
/*mean_perct_sellerrating_clust4*/
/*mean_perct_sellerrating_clust5*/
/*mean_product_star_rate_presto*/
/*mean_salesrank_categ_presto*/
/*mean_salesrank_subcateg_presto*/
/*mean_seller_star_rating_clust1*/
/*mean_seller_star_rating_clust2*/
/*mean_seller_star_rating_clust3*/
/*mean_seller_star_rating_clust4*/
/*mean_seller_star_rating_clust5*/
/*mean_uniq_ASIN_clust1*/
/*mean_uniq_ASIN_clust2*/
/*mean_uniq_ASIN_clust3*/
/*mean_uniq_ASIN_clust4*/
/*mean_uniq_ASIN_clust5*/
/*min_Product_reviews_presto*/
/*min_ans_questions_presto*/
/*min_buyboxprice_presto*/
/*min_cumsalefreq_clust1*/
/*min_cumsalefreq_clust2*/
/*min_cumsalefreq_clust3*/
/*min_cumsalefreq_clust4*/
/*min_cumsalefreq_clust5*/
/*min_num_seller_rating_clust1*/
/*min_num_seller_rating_clust2*/
/*min_num_seller_rating_clust3*/
/*min_num_seller_rating_clust4*/
/*min_num_seller_rating_clust5*/
/*min_perct_sellerrating_clust1*/
/*min_perct_sellerrating_clust2*/
/*min_perct_sellerrating_clust3*/
/*min_perct_sellerrating_clust4*/
/*min_perct_sellerrating_clust5*/
/*min_product_star_rate_presto*/
/*min_salesrank_categ_presto*/
/*min_salesrank_subcateg_presto*/
/*min_seller_star_rating_clust1*/
/*min_seller_star_rating_clust2*/
/*min_seller_star_rating_clust3*/
/*min_seller_star_rating_clust4*/
/*min_seller_star_rating_clust5*/
/*min_uniq_ASIN_clust1*/
/*min_uniq_ASIN_clust2*/
/*min_uniq_ASIN_clust3*/
/*min_uniq_ASIN_clust4*/
/*min_uniq_ASIN_clust5*/
/*num_EPamzn*/
/*num_EPclust1*/
/*num_EPclust2*/
/*num_EPclust3*/
/*num_EPclust4*/
/*num_EPclust5*/
/*num_EPused3p*/
/*num_EPusedamzn*/
/*num_FBA_clust1*/
/*num_FBA_clust2*/
/*num_FBA_clust3*/
/*num_FBA_clust4*/
/*num_FBA_clust5*/
/*num_Freeship_clust1*/
/*num_Freeship_clust2*/
/*num_Freeship_clust3*/
/*num_Freeship_clust4*/
/*num_Freeship_clust5*/
/*num_HBeachamzn*/
/*num_HBeachclust1*/
/*num_HBeachclust2*/
/*num_HBeachclust3*/
/*num_HBeachclust4*/
/*num_HBeachclust5*/
/*num_HBeachused3p*/
/*num_HBeachusedamzn*/
/*num_csnartamzn*/
/*num_csnartclust1*/
/*num_csnartclust2*/
/*num_csnartclust3*/
/*num_csnartclust4*/
/*num_csnartclust5*/
/*num_csnartused3p*/
/*num_csnartusedamzn*/
/*num_pricechange1EPamzn*/
/*num_pricechange1EPclust1*/
/*num_pricechange1EPclust2*/
/*num_pricechange1EPclust3*/
/*num_pricechange1EPclust4*/
/*num_pricechange1EPclust5*/
/*num_pricechange1EPused3p*/
/*num_pricechange1EPusedamzn*/
/*num_pricechange1Hbeachamzn*/
/*num_pricechange1Hbeachclust1*/
/*num_pricechange1Hbeachclust2*/
/*num_pricechange1Hbeachclust3*/
/*num_pricechange1Hbeachclust4*/
/*num_pricechange1Hbeachclust5*/
/*num_pricechange1Hbeachused3p*/
/*num_pricechange1Hbeachusedamzn*/
/*num_pricechange1csnartamzn*/
/*num_pricechange1csnartclust1*/
/*num_pricechange1csnartclust2*/
/*num_pricechange1csnartclust3*/
/*num_pricechange1csnartclust4*/
/*num_pricechange1csnartclust5*/
/*num_pricechange1csnartused3p*/
/*num_pricechange1csnartusedamzn*/
/*num_pricechange1prestoamzn*/
/*num_pricechange1prestoclust1*/
/*num_pricechange1prestoclust2*/
/*num_pricechange1prestoclust3*/
/*num_pricechange1prestoclust4*/
/*num_pricechange1prestoclust5*/
/*num_pricechange1prestoused3p*/
/*num_pricechange1prestousedamzn*/
/*num_pricechange1securaamzn*/
/*num_pricechange1securaclust1*/
/*num_pricechange1securaclust2*/
/*num_pricechange1securaclust3*/
/*num_pricechange1securaclust4*/
/*num_pricechange1securaclust5*/
/*num_pricechange1securaused3p*/
/*num_pricechange1securausedamzn*/
/*num_pricechange1tfalamzn*/
/*num_pricechange1tfalclust1*/
/*num_pricechange1tfalclust2*/
/*num_pricechange1tfalclust3*/
/*num_pricechange1tfalclust4*/
/*num_pricechange1tfalclust5*/
/*num_pricechange1tfalused3p*/
/*num_pricechange1tfalusedamzn*/
/*num_securaamzn*/
/*num_securaclust1*/
/*num_securaclust2*/
/*num_securaclust3*/
/*num_securaclust4*/
/*num_securaclust5*/
/*num_securaused3p*/
/*num_securausedamzn*/
/*num_tfalamzn*/
/*num_tfalclust1*/
/*num_tfalclust2*/
/*num_tfalclust3*/
/*num_tfalclust4*/
/*num_tfalclust5*/
/*num_tfalused3p*/
/*num_tfalusedamzn*/
/*product_star_EP*/
/*product_star_Hbeach*/
/*product_star_csnart*/
/*product_star_secura*/
/*product_star_tfal);*/
/*set  merge_finaldata2;*/
/*run;*/

proc contents data = amazon_kitchnequip3;
/*run;*/


/**merging back seasonality indicator to the modeling data ***/

proc sort data = amazon_kitchnequip3 (keep = time1 season) nodupkey out= seasonality_data;
by time1;
run;
data mvrf_model1;
merge merge_finaldata2 (in=a) seasonality_data (in=b);
by time1 ;
if a and b;
run;

/***ADDING External site data*****/

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\homedepot_merged\HD_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= HD_pricechange_allcateg
REPLACE;
run;

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Walmart_merged\walmart_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= wm_pricechange_allcateg
REPLACE;
run;

PROC import 
DATAfile ="D:\Amazon Price Dynamics\New Data\Data Scraped\Sears_merged\Sears_Pricechangesallcateg_4-6-20.csv" 
DBMS=csv
OUT= sears_pricechange_allcateg
REPLACE;
run;
data sears_pricechange_allcateg;
set sears_pricechange_allcateg;
rename date = time1;
run;
proc sql;
create table  HD as
select 
time1,
categ,
max(pricechange) as max_pricechange_HD,
mean(pricechange) as mean_pricechange_HD,
min(pricechange) as min_pricechange_HD
from
HD_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;

data HD1;
set HD;
if abs(min_pricechange_HD) > abs(max_pricechange_HD) then maxpricechange_HD = min_pricechange_HD;
else if abs(min_pricechange_HD) < abs(max_pricechange_HD) then maxpricechange_HD = max_pricechange_HD;
else maxpricechange_HD = 0;
run;
proc sql;
create table WM as
select 
time1,
categ,
max(pricechange) as max_pricechange_wm,
mean(pricechange) as mean_pricechange_wm,
min(pricechange) as min_pricechange_wm
from
wm_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;
data wm1;
set wm;
if abs(min_pricechange_wm) > abs(max_pricechange_wm) then maxpricechange_wm = min_pricechange_wm;
else if abs(min_pricechange_wm) < abs(max_pricechange_wm) then maxpricechange_wm = max_pricechange_wm;
else maxpricechange_wm = 0;
run;
proc freq data = wm1;
tables time1;run;

proc sql;
create table Sears as
select 
time1,
categ,
max(pricechange) as max_pricechange_sears,
mean(pricechange) as mean_pricechange_sears,
min(pricechange) as min_pricechange_sears
from
sears_pricechange_allcateg
where time1 >= '14Dec2017'd and time1 <= '06May2018'd 
group by
time1,
categ;
quit;
data sears1;
set sears;
if abs(min_pricechange_sears) > abs(max_pricechange_sears) then maxpricechange_sears = min_pricechange_sears;
else if abs(min_pricechange_sears) < abs(max_pricechange_sears) then maxpricechange_sears = max_pricechange_sears;
else maxpricechange_sears = 0;
run;

/*data merged_allwebsites (drop= min_pricechange_HD max_pricechange_HD min_pricechange_wm max_pricechange_wm*/
/*min_pricechange_sears max_pricechange_sears);*/
/*merge HD1 (in=a) WM1 (in=b) Sears1 (in=c);*/
/*by time1 categ;*/
/*if a and b and c;*/
/*if abs(maxpricechange_HD)= max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then  maxpricechange_externalsite = maxpricechange_HD;*/
/*else if abs(maxpricechange_WM) = max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_WM;*/
/*else if abs(maxpricechange_sears) = max(abs(maxpricechange_WM),abs(maxpricechange_HD),abs(maxpricechange_sears)) then maxpricechange_externalsite = maxpricechange_sears;*/
/*else maxpricechange_externalsite = 0;*/
/*meanpricechange_externalsite = mean(mean_pricechange_HD, mean_pricechange_WM,mean_pricechange_sears);*/
/*run;*/

data merged_allwebsites (drop= min_pricechange_HD max_pricechange_HD min_pricechange_wm max_pricechange_wm
min_pricechange_sears max_pricechange_sears);
merge HD1 (in=a) WM1 (in=b) Sears1 (in=c);
by time1 categ;
if a and b and c;
run;

/**export this above file for all other categories**/
/**extracting only Deep fryer categ***/

data external_deepfryerprices;
set merged_allwebsites;
where categ = 'Deep Fryer';
run;
/**/

/**/
/*data mvrfdata_withexternalsite;*/
/*merge mvrf_model1 (in=a) external_deepfryerprices (in=b);*/
/*by time1;*/
/*if a;*/
/*if a and not b then do;*/
/*externalsite_ind = 0;*/
/*maxpricechange_extsite_lag1 = 0;*/
/*meanpricechange_extsite_lag1 = 0;*/
/*end;*/
/*else externalsite_ind = 1;*/
/*run;*/
/*proc contents data = mvrfdata_withexternalsite;*/
/*run;*/


/**merging with edited external data ***/

proc sort data = mvrf_model1;
by time1;
run;

proc sort data = external_deepfryerprices (drop = categ) out = externalsite_data2;
by time1;
run;
data mvrfdata_withexternalsite1;
merge mvrf_model1 (in=a) externalsite_data2 (in=b);
by time1;
if a;

if a and not b then do;
ext_site = 0;
maxpricechange_HD = 0;   
maxpricechange_sears = 0;     
maxpricechange_wm = 0;     
mean_pricechange_HD = 0;     
mean_pricechange_sears = 0;     
mean_pricechange_wm = 0;
end;
else ext_site =1;
run;
proc freq data = mvrfdata_withexternalsite1;
tables ext_site;
run;
proc contents data = mvrfdata_withexternalsite1;
run;

/***DAY OF THE WEEK IDENTIFICATION**/

data mvrfdata_withexternalsite1;
set mvrfdata_withexternalsite1;
day_week = weekday(time1);
if day_week = 1 then sunday = 1; else sunday = 0;
if day_week = 2 then monday = 1; else monday = 0;
if day_week = 3 then tuesday = 1; else tuesday = 0;
if day_week = 4 then wednesday = 1; else wednesday = 0;
if day_week = 5 then thursday = 1; else thursday = 0;
if day_week = 6 then friday = 1; else friday = 0;
if day_week = 7 then saturday = 1; else saturday = 0;
if day_week in (1,7) then weekend = 1; else weekend = 0;
run;

proc contents data = mvrfdata_withexternalsite1;
run;
/***lagging external site price changes****/ 
data mvrfdata_withexternalsite2;
set mvrfdata_withexternalsite1;
maxpricechange_HDlag1 = lag(maxpricechange_HD);   
maxpricechange_searslag1 = lag(maxpricechange_sears);   
maxpricechange_wmlag1 = lag(maxpricechange_wm);
maxpricechange_HDlag2 = lag2(maxpricechange_HD);   
maxpricechange_searslag2 = lag2(maxpricechange_sears);   
maxpricechange_wmlag2 = lag2(maxpricechange_wm);
maxpricechange_HDlag3 = lag3(maxpricechange_HD);   
maxpricechange_searslag3 = lag3(maxpricechange_sears);   
maxpricechange_wmlag3 = lag3(maxpricechange_wm);
run;

data mvrfdata_withexternalsite2;
set mvrfdata_withexternalsite2;
top_salesrank = min(ifn(salerank_EP_lag1=0,.,salerank_EP_lag1),
                    ifn(maxsalerank_presto_lag1=0,.,maxsalerank_presto_lag1),
                    ifn(meansalerank_presto_lag1=0,.,meansalerank_presto_lag1), 
                    ifn(minsalerank_presto_lag1=0,.,minsalerank_presto_lag1),
                    ifn(salerank_Hbeach_lag1=0,.,salerank_Hbeach_lag1),
                    ifn(salerank_csnart_lag1=0,.,salerank_csnart_lag1),
					ifn(salerank_secura_lag1=0,.,salerank_secura_lag1),
                    ifn(salerank_tfal_lag1=0,.,salerank_tfal_lag1));

bottom_salesrank = max(ifn(salerank_EP_lag1=0,.,salerank_EP_lag1),
                    ifn(maxsalerank_presto_lag1=0,.,maxsalerank_presto_lag1),
                    ifn(meansalerank_presto_lag1=0,.,meansalerank_presto_lag1), 
                    ifn(minsalerank_presto_lag1=0,.,minsalerank_presto_lag1),
                    ifn(salerank_Hbeach_lag1=0,.,salerank_Hbeach_lag1),
                    ifn(salerank_csnart_lag1=0,.,salerank_csnart_lag1),
					ifn(salerank_secura_lag1=0,.,salerank_secura_lag1),
                    ifn(salerank_tfal_lag1=0,.,salerank_tfal_lag1));

run;


data mvrfdata_withexternalsite3;
set mvrfdata_withexternalsite2;

if top_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if salerank_EP_lag1 = top_salesrank then ep_topbrandlag1 = 1; else ep_topbrandlag1 = 0; 
if minsalerank_presto_lag1 = top_salesrank then presto_topbrandlag1 = 1; else presto_topbrandlag1 = 0; 
if salerank_Hbeach_lag1 = top_salesrank then hbeach_topbrandlag1 = 1; else hbeach_topbrandlag1 = 0; 
if salerank_csnart_lag1 = top_salesrank then csnart_topbrandlag1 = 1; else csnart_topbrandlag1 = 0; 
if salerank_secura_lag1 = top_salesrank then secura_topbrandlag1 = 1; else secura_topbrandlag1 = 0; 
if salerank_tfal_lag1 = top_salesrank then tfal_topbrandlag1 = 1; else tfal_topbrandlag1 = 0; 
end;

if bottom_salesrank ne . and top_salesrank ne bottom_salesrank then do;
if salerank_EP_lag1 = bottom_salesrank then ep_bottombrandlag1 = 1; else ep_bottombrandlag1 = 0; 
if minsalerank_presto_lag1 = bottom_salesrank then presto_bottombrandlag1 = 1; else presto_bottombrandlag1 = 0; 
if salerank_Hbeach_lag1 = bottom_salesrank then hbeach_bottombrandlag1 = 1; else hbeach_bottombrandlag1 = 0; 
if salerank_csnart_lag1 = bottom_salesrank then csnart_bottombrandlag1 = 1; else csnart_bottombrandlag1 = 0; 
if salerank_secura_lag1 = bottom_salesrank then secura_bottombrandlag1 = 1; else secura_bottombrandlag1 = 0; 
if salerank_tfal_lag1 = bottom_salesrank then tfal_bottombrandlag1 = 1; else tfal_bottombrandlag1 = 0;
end;

else do;
ep_topbrandlag1 = 0;
presto_topbrandlag1 = 0; 
hbeach_topbrandlag1 = 0; 
csnart_topbrandlag1 = 0; 
secura_topbrandlag1 = 0;
tfal_topbrandlag1 = 0;
ep_bottombrandlag1 = 0;
presto_bottombrandlag1 = 0; 
hbeach_bottombrandlag1 = 0; 
csnart_bottombrandlag1 = 0; 
secura_bottombrandlag1 = 0;
tfal_bottombrandlag1 = 0;
end;

run;
proc print data = final_gam_modeldata (obs=100);
var Y_clust5_tfal;
run;

/***creating main effect of price change**/
data final_gam_modeldata;
set mvrfdata_withexternalsite3;

/*price increase/decrease of 5% and -5%, with direction**/
/**Cuisinart**/
if Y_amzn_csnart_lag1 >= 0.05 and Y_amzn_csnart_lag1 < 0.1 then incr5per_amzn_csnart = 1; else incr5per_amzn_csnart = 0;
if Y_amzn_csnart_lag1 <= -0.05 and Y_amzn_csnart_lag1 > -0.1 then decr5per_amzn_csnart = 1; else decr5per_amzn_csnart = 0;

if Y_amzn_csnart_lag1 >= 0.1 and Y_amzn_csnart_lag1 < 0.2 then incr10per_amzn_csnart = 1; else incr10per_amzn_csnart = 0;
if Y_amzn_csnart_lag1 <= -0.1 and Y_amzn_csnart_lag1 > -0.2 then decr10per_amzn_csnart = 1; else decr10per_amzn_csnart = 0;

if Y_amzn_csnart_lag1 >= 0.2 then incr20per_amzn_csnart = 1; else incr20per_amzn_csnart = 0;
if Y_amzn_csnart_lag1 <= -0.2 then decr20per_amzn_csnart = 1; else decr20per_amzn_csnart = 0;

if Y_clust1_csnart_lag1 >= 0.05 and Y_clust1_csnart_lag1 < 0.1 then incr5per_clust1_csnart = 1; else incr5per_clust1_csnart = 0;
if Y_clust3_csnart_lag1 >= 0.05 and Y_clust3_csnart_lag1 < 0.1 then incr5per_clust3_csnart = 1; else incr5per_clust3_csnart = 0;
if Y_clust5_tfal_lag1 >= 0.05 and Y_clust5_tfal_lag1 < 0.1 then incr5per_clust5_tfal = 1; else incr5per_clust5_tfal = 0;


if Y_clust1_csnart_lag1 <= -0.05 and Y_clust1_csnart_lag1 > -0.1 then decr5per_clust1_csnart = 1; else decr5per_clust1_csnart = 0;
if Y_clust3_csnart_lag1 <= -0.05 and Y_clust3_csnart_lag1 > -0.1 then decr5per_clust3_csnart = 1; else decr5per_clust3_csnart = 0;
if Y_clust5_tfal_lag1 <= -0.05 and Y_clust5_tfal_lag1 > -0.1 then decr5per_clust5_tfal = 1; else decr5per_clust5_tfal = 0;

if Y_clust1_csnart_lag1 >= 0.1 and Y_clust1_csnart_lag1 < 0.2 then incr10per_clust1_csnart = 1; else incr10per_clust1_csnart = 0;
if Y_clust3_csnart_lag1 >= 0.1 and Y_clust3_csnart_lag1 < 0.2 then incr10per_clust3_csnart = 1; else incr10per_clust3_csnart = 0;
if Y_clust5_tfal_lag1 >= 0.1 and Y_clust5_tfal_lag1 < 0.2 then incr10per_clust5_tfal = 1; else incr10per_clust5_tfal = 0;

if Y_clust1_csnart_lag1 <= -0.1 and Y_clust1_csnart_lag1 > -0.2 then decr10per_clust1_csnart = 1; else decr10per_clust1_csnart = 0;
if Y_clust3_csnart_lag1 <= -0.1 and Y_clust3_csnart_lag1 > -0.2 then decr10per_clust3_csnart = 1; else decr10per_clust3_csnart = 0;
if Y_clust5_tfal_lag1 <= -0.1 and Y_clust5_tfal_lag1 > -0.2 then decr10per_clust5_tfal = 1; else decr10per_clust5_tfal = 0;

if Y_clust1_csnart_lag1 >= 0.2 then incr20per_clust1_csnart = 1; else incr20per_clust1_csnart = 0;
if Y_clust3_csnart_lag1 >= 0.2 then incr20per_clust3_csnart = 1; else incr20per_clust3_csnart = 0;
if Y_clust5_tfal_lag1 >= 0.2 then incr20per_clust5_tfal = 1; else incr20per_clust5_tfal = 0;


if Y_clust1_csnart_lag1 <= -0.2 then decr20per_clust1_csnart = 1; else decr20per_clust1_csnart = 0;
if Y_clust3_csnart_lag1 <= -0.2 then decr20per_clust3_csnart = 1; else decr20per_clust3_csnart = 0;
if Y_clust5_tfal_lag1 <= -0.2 then decr20per_clust5_tfal = 1; else decr20per_clust5_tfal = 0;

/**Hamilton Beach**/
if Y_amzn_hbeach_lag1 >= 0.05 and Y_amzn_hbeach_lag1 < 0.1 then incr5per_amzn_hbeach = 1; else incr5per_amzn_hbeach = 0;
if Y_amzn_hbeach_lag1 <= -0.05 and Y_amzn_hbeach_lag1 > -0.1 then decr5per_amzn_hbeach = 1; else decr5per_amzn_hbeach = 0;

if Y_amzn_hbeach_lag1 >= 0.1 and Y_amzn_hbeach_lag1 < 0.2 then incr10per_amzn_hbeach = 1; else incr10per_amzn_hbeach = 0;
if Y_amzn_hbeach_lag1 <= -0.1 and Y_amzn_hbeach_lag1 > -0.2 then decr10per_amzn_hbeach = 1; else decr10per_amzn_hbeach = 0;

if Y_amzn_hbeach_lag1 >= 0.2 then incr20per_amzn_hbeach = 1; else incr20per_amzn_hbeach = 0;
if Y_amzn_hbeach_lag1 <= -0.2 then decr20per_amzn_hbeach = 1; else decr20per_amzn_hbeach = 0;

/**Presto**/
if Y_amzn_presto_lag1 >= 0.05 and Y_amzn_presto_lag1 < 0.1 then incr5per_amzn_presto = 1; else incr5per_amzn_presto = 0;
if Y_amzn_presto_lag1 <= -0.05 and Y_amzn_presto_lag1 > -0.1 then decr5per_amzn_presto = 1; else decr5per_amzn_presto = 0;

if Y_amzn_presto_lag1 >= 0.1 and Y_amzn_presto_lag1 < 0.2 then incr10per_amzn_presto = 1; else incr10per_amzn_presto = 0;
if Y_amzn_presto_lag1 <= -0.1 and Y_amzn_presto_lag1 > -0.2 then decr10per_amzn_presto = 1; else decr10per_amzn_presto = 0;

if Y_amzn_presto_lag1 >= 0.2 then incr20per_amzn_presto = 1; else incr20per_amzn_presto = 0;
if Y_amzn_presto_lag1 <= -0.2 then decr20per_amzn_presto = 1; else decr20per_amzn_presto = 0;

/**Tfal**/
if Y_amzn_tfal_lag1 >= 0.05 and Y_amzn_tfal_lag1 < 0.1 then incr5per_amzn_tfal = 1; else incr5per_amzn_tfal = 0;
if Y_amzn_tfal_lag1 <= -0.05 and Y_amzn_tfal_lag1 > -0.1 then decr5per_amzn_tfal = 1; else decr5per_amzn_tfal = 0;

if Y_amzn_tfal_lag1 >= 0.1 and Y_amzn_tfal_lag1 < 0.2 then incr10per_amzn_tfal = 1; else incr10per_amzn_tfal = 0;
if Y_amzn_tfal_lag1 <= -0.1 and Y_amzn_tfal_lag1 > -0.2 then decr10per_amzn_tfal = 1; else decr10per_amzn_tfal = 0;

if Y_amzn_tfal_lag1 >= 0.2 then incr20per_amzn_tfal = 1; else incr20per_amzn_tfal = 0;
if Y_amzn_tfal_lag1 <= -0.2 then decr20per_amzn_tfal = 1; else decr20per_amzn_tfal = 0;

if Y_clust2_tfal_lag1 >= 0.05 and Y_clust2_tfal_lag1 < 0.1 then incr5per_3p_tfal = 1; else incr5per_3p_tfal = 0;
if Y_clust2_tfal_lag1 <= -0.05 and Y_clust2_tfal_lag1 > -0.1 then decr5per_3p_tfal = 1; else decr5per_3p_tfal = 0;

if Y_clust2_tfal_lag1 >= 0.1 and Y_clust2_tfal_lag1 < 0.2 then incr10per_3p_tfal = 1; else incr10per_3p_tfal = 0;
if Y_clust2_tfal_lag1 <= -0.1 and Y_clust2_tfal_lag1 > -0.2 then decr10per_3p_tfal = 1; else decr10per_3p_tfal = 0;

if Y_clust2_tfal_lag1 >= 0.2 then incr20per_3p_tfal = 1; else incr20per_3p_tfal = 0;
if Y_clust2_tfal_lag1 <= -0.2 then decr20per_3p_tfal = 1; else decr20per_3p_tfal = 0;

/***Secura**/

if Y_clust4_secura_lag1 >= 0.05 and Y_clust4_secura_lag1 < 0.1 then incr5per_3p_sec = 1; else incr5per_3p_sec = 0;
if Y_clust4_secura_lag1 <= -0.05 and Y_clust4_secura_lag1 > -0.1 then decr5per_3p_sec = 1; else decr5per_3p_sec = 0;

if Y_clust4_secura_lag1 >= 0.1 and Y_clust4_secura_lag1 < 0.2 then incr10per_3p_sec = 1; else incr10per_3p_sec = 0;
if Y_clust4_secura_lag1 <= -0.1 and Y_clust4_secura_lag1 > -0.2 then decr10per_3p_sec = 1; else decr10per_3p_sec = 0;

if Y_clust4_secura_lag1 >= 0.2 then incr20per_3p_sec = 1; else incr20per_3p_sec = 0;
if Y_clust4_secura_lag1 <= -0.2 then decr20per_3p_sec = 1; else decr20per_3p_sec = 0;
run;

/**/
/*proc sql;*/
/*create table summ_num_changes as*/
/*select*/
/*sum(case when Y_amzn_csnart_lag1 ne 0 then 1 else 0 end) as num_amzn_csnartchanges,*/
/*sum(case when Y_clust1_csnart_lag1 ne 0 then 1 else 0 end) as num_3p1_csnartchanges,*/
/*sum(case when Y_clust3_csnart_lag1 ne 0 then 1 else 0 end) as num_3p3_csnartchanges,*/
/*sum(case when Y_clust5_csnart_lag1 ne 0 then 1 else 0 end) as num_3p5_csnartchanges,*/
/*sum(case when Y_amzn_hbeach_lag1 ne 0 then 1 else 0 end) as num_amzn_hbeachchanges,*/
/*sum(case when Y_amzn_presto_lag1 ne 0 then 1 else 0 end) as num_amzn_prestochanges,*/
/*sum(case when Y_amzn_tfal_lag1 ne 0 then 1 else 0 end) as num_amzn_tfalchanges,*/
/*sum(case when Y_clust2_tfal_lag1 ne 0 then 1 else 0 end) as num_3p2_tfalchanges,*/
/*sum(case when Y_clust4_secura_lag1 ne 0 then 1 else 0 end) as num_3p4_securachanges,*/
/**/
/*sum(case when incr5per_amzn_csnart ne 0 then 1 else 0 end) as num_incr5peramzn_csnartchanges,*/
/*sum(case when decr5per_amzn_csnart ne 0 then 1 else 0 end) as num_decr5peramzn_csnartchanges,*/
/*sum(case when incr10per_amzn_csnart ne 0 then 1 else 0 end) as num_incr10peramzn_csnartchanges,*/
/*sum(case when decr10per_amzn_csnart ne 0 then 1 else 0 end) as num_decr10peramzn_csnartchanges,*/
/*sum(case when incr20per_amzn_csnart ne 0 then 1 else 0 end) as num_incr20peramzn_csnartchanges,*/
/*sum(case when decr20per_amzn_csnart ne 0 then 1 else 0 end) as num_decr20peramzn_csnartchanges,*/
/**/
/*sum(case when incr5per_3p_csnart ne 0 then 1 else 0 end) as num_incr5per3p_csnartchanges,*/
/*sum(case when decr5per_3p_csnart ne 0 then 1 else 0 end) as num_decr5per3p_csnartchanges,*/
/*sum(case when incr10per_3p_csnart ne 0 then 1 else 0 end) as num_incr10per3p_csnartchanges,*/
/*sum(case when decr10per_3p_csnart ne 0 then 1 else 0 end) as num_decr10per3p_csnartchanges,*/
/*sum(case when incr20per_3p_csnart ne 0 then 1 else 0 end) as num_incr20per3p_csnartchanges,*/
/*sum(case when decr20per_3p_csnart ne 0 then 1 else 0 end) as num_decr20per3p_csnartchanges,*/
/**/
/*sum(case when incr5per_amzn_hbeach ne 0 then 1 else 0 end) as num_incr5peramzn_hbeachchanges,*/
/*sum(case when decr5per_amzn_hbeach ne 0 then 1 else 0 end) as num_decr5peramzn_hbeachchanges,*/
/*sum(case when incr10per_amzn_hbeach ne 0 then 1 else 0 end) as num_incr10peramzn_hbeachchanges,*/
/*sum(case when decr10per_amzn_hbeach ne 0 then 1 else 0 end) as num_decr10peramzn_hbeachchanges,*/
/*sum(case when incr20per_amzn_hbeach ne 0 then 1 else 0 end) as num_incr20peramzn_hbeachchanges,*/
/*sum(case when decr20per_amzn_hbeach ne 0 then 1 else 0 end) as num_decr20peramzn_hbeachchanges,*/
/**/
/*sum(case when incr5per_amzn_presto ne 0 then 1 else 0 end) as num_incr5peramzn_prestochanges,*/
/*sum(case when decr5per_amzn_presto ne 0 then 1 else 0 end) as num_decr5peramzn_prestochanges,*/
/*sum(case when incr10per_amzn_presto ne 0 then 1 else 0 end) as num_incr10peramzn_prestochanges,*/
/*sum(case when decr10per_amzn_presto ne 0 then 1 else 0 end) as num_decr10peramzn_prestochanges,*/
/*sum(case when incr20per_amzn_presto ne 0 then 1 else 0 end) as num_incr20peramzn_prestochanges,*/
/*sum(case when decr20per_amzn_presto ne 0 then 1 else 0 end) as num_decr20peramzn_prestochanges,*/
/**/
/*sum(case when incr5per_amzn_tfal ne 0 then 1 else 0 end) as num_incr5peramzn_tfalchanges,*/
/*sum(case when decr5per_amzn_tfal ne 0 then 1 else 0 end) as num_decr5peramzn_tfalchanges,*/
/*sum(case when incr10per_amzn_tfal ne 0 then 1 else 0 end) as num_incr10peramzn_tfalchanges,*/
/*sum(case when decr10per_amzn_tfal ne 0 then 1 else 0 end) as num_decr10peramzn_tfalchanges,*/
/*sum(case when incr20per_amzn_tfal ne 0 then 1 else 0 end) as num_incr20peramzn_tfalchanges,*/
/*sum(case when decr20per_amzn_tfal ne 0 then 1 else 0 end) as num_decr20peramzn_tfalchanges,*/
/**/
/*sum(case when incr5per_3p_tfal ne 0 then 1 else 0 end) as num_incr5per3p_tfalchanges,*/
/*sum(case when decr5per_3p_tfal ne 0 then 1 else 0 end) as num_decr5per3p_tfalchanges,*/
/*sum(case when incr10per_3p_tfal ne 0 then 1 else 0 end) as num_incr10per3p_tfalchanges,*/
/*sum(case when decr10per_3p_tfal ne 0 then 1 else 0 end) as num_decr10per3p_tfalchanges,*/
/*sum(case when incr20per_3p_tfal ne 0 then 1 else 0 end) as num_incr20per3p_tfalchanges,*/
/*sum(case when decr20per_3p_tfal ne 0 then 1 else 0 end) as num_decr20per3p_tfalchanges,*/
/**/
/*sum(case when incr5per_3p_sec ne 0 then 1 else 0 end) as num_incr5per3p_secchanges,*/
/*sum(case when decr5per_3p_sec ne 0 then 1 else 0 end) as num_decr5per3p_secchanges,*/
/*sum(case when incr10per_3p_sec ne 0 then 1 else 0 end) as num_incr10per3p_secchanges,*/
/*sum(case when decr10per_3p_sec ne 0 then 1 else 0 end) as num_decr10per3p_secchanges,*/
/*sum(case when incr20per_3p_sec ne 0 then 1 else 0 end) as num_incr20per3p_secchanges,*/
/*sum(case when decr20per_3p_sec ne 0 then 1 else 0 end) as num_decr20per3p_secchanges*/
/*from */
/*final_gam_modeldata;*/
/*quit;*/


PROC EXPORT 
DATA=final_gam_modeldata
DBMS=csv
OUTFILE= "D:\Amazon Price Dynamics\New Data\GAM + MVRF\Other categories\Deep Fryers\final_gam_modeldata_jmrnd4.csv"
REPLACE;
run;
/*libname inn "D:\Amazon Price Dynamics\New Data\GAM + MVRF\Other categories\Deep Fryers";*/
/*data inn.final_gam_modeldata;*/
/*set final_gam_modeldata;*/
/*run;*/

